|
|
Posted by Tom on 03/12/07 17:13
On 10 Mar 2007 14:44:17 -0800, Alex wrote...
>
>On Mar 10, 11:24 pm, "Paul Lautman" <paul.laut...@btinternet.com>
>wrote:
>> Alex wrote:
>> > Hey everyone,
>>
>> > I'm working on a project that will let the users store their budget,
>> > and in the backbone I have a MySQL database. However, I'm not really
>> > fluent in SQL. I have two questions:
>>
>> > The first is a request for a suggestion of a SQL query. I have a table
>> > that contains all user entries in the following format:
>>
>> > id member_id date amount
>> > 1 1 070210 2.3
>> > 2 1 070210 4.3
>> > 3 2 070211 5.8
>> > 4 2 070212 1.6
>> > 5 1 070213 3.2
>>
>> > In order to sum up the total amount for a specific member, I use the
>> > the following query:
>>
>> > SELECT SUM(amount) as amount FROM budget WHERE member_id=1
>>
>> > Now how do I solve this if I'd like to limit the SUM to count the
>> > amount in entries between specific id's? The reason for this is that I
>> > have a table that shows every member's expenses. The number of rows
>> > can be set by the member and is taken care of with a LIMIT command in
>> > that function, but the same method isn't working in this query. Any
>> > ideas of how to solve this?
>>
>> > Then I have a "follow-up" question. When I use the SUM function on the
>> > amounts above, I end up with a number with loads of decimals when I'd
>> > only like one. Right now I'm solving this with a ROUND command. The
>> > types of the column is FLOAT. Any ideas?
>>
>> > Thanks a lot in advance.
>>
>> Can you give and example based on the above table of what you mean by
>> "between specific id's" and what you would expect the result set to like
>> like?
>>
>> Also what do you mean by "loads of decimals"?
>
>Well, I'd like to get the sum of say person 1's last two amounts, that
>would be the sum of amount in rows 2 and 5 (summing 7.5 in this
>example).
>
>As for the decimals, when I use the query above I get something like
>9.7999988079 instead of 9.8.
>
You might be able to use "groups by member_id" to get a sum for each member. If
you use a limit there, that's likely going to limit your results to two members,
so you can probably add a condition based on the date information, to limit the
results on an individual basis.
Wasn't sure which SQL you were using, but with MySQL and PostgreSQL you can use
something like "round(SUM(amount), 2)" to limit a decimal to 2 digits.
Tom
--
Newsguy.com
Basic Accounts $39.95 / 12 months
Navigation:
[Reply to this message]
|