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.

On Mar 10, 7:11 pm, "Alex" <alex.wald... (AT) gmail (DOT) com> wrote:
First I suggest you learn some relational database design. The id
column in your table is not necessary. A better Primary key looks to
be the combined member_id and date. DO a GOOGLE search on Codd and
Database Normalization.

Deleted?
updated?
Whiuch rows are you talking about??

Second, LIMIT is a MySQL extension, not standard SQL. It is a crutch
and you are starting to dependf upon it too much.
Not knowing the features of LIMIT in MySQL, I suggest you learn how to
use a cursor in your function. Barring that, can you create a query
that returns only the rows you want? then to get the sum use that
query as a VIEW and run the some on the VIEW. Some DBMS products
support in-line views like this:

SELECT SUM(amount)
FROM (SELECT amount FROM thistable WHERE member_id=1 limit 10)

assuming that's how LIMIT works. But you will have better control
programming a CURSOR.

This is a display issue. Does MySQL have any other display formatting
features? ROUND seems as good as any other way, as long as it meets
your needs.

You are welcome. Hope that helps.
ed

Alex wrote:
[...]
SELECT SUM(amount) as amount FROM budget
WHERE member_id=1 and id between ? AND ?

If between is not supported you can use <= and >= to do the same thing.

Do the formatting on the client. Otherwise I think ROUND will do the job
(eventhough I dont know what ROUND does using mysql).


/Lennart

Ed Prochak wrote:
In general I agree, but in this case id 1 and 2 would violate the
suggested primary key. Unless each member can do only one *transaction*
per date, it will not be possible to use date in the key.

BTW, date is all kind of things in sql (type, function for starters),
hence not a really good name for a column.


/Lennart

[...]

Lennart wrote:
[...]
correction, should be:
it will not be possible to use (date, member) as a key.

/Lennart

Alex wrote:
Lennart wrote:
The real problem is storing financial data as a floating point value. Use
fixed point.

Blithely talking about using ROUND for financial data begs the question of
whether it is important for money to be handled with exactitude. Isn't it?

-- Lew

On Mar 11, 3:38 am, Lennart <erik.lennart.jons... (AT) gmail (DOT) com> wrote:
I guess I am just too use to ORACLE DATE type which includes time.

But the point is having some generic ID just to be the PK is a clear
mistake. Even if he just renamed it to something like Transaction_id,
then it might mean something in relationship to the Entiry that
represents.

Good point on the date column name.
Ed