Yes, I can see how this would work for just the one order and hardcoding the
100... but I cannot assume only to sum distinct values and my table has
other order_refs in it with the same multiple rows of over multiple days, so
I need a more generic select that will list this nice summary for all
orders... do you see what I mean?
e.g.
id | date | order_ref | amount
1 | 1/1/01 | 100 | 1000 << these 2 are the rows
2 | 1/1/01 | 100 | 200 << i want to exclude for order 100
3 | 2/1/01 | 100 | 1000
4 | 2/1/01 | 100 | 200
5 | 2/1/01 | 100 | 50
6 | 2/1/01 | 101 | 10000 << i also need to exclude these 2 rows
7 | 2/1/01 | 101 | 2000 << out of the calculation for order 101
8 | 2/1/01 | 101 | 10000
9 | 3/1/01 | 101 | 2000
10 | 3/1/01 | 101 | 500
and I want to end up with
latest_date_on_order | order_ref | sum(amount)
2/1/01 | 100 | 1250
3/1/01 | 101 | 12500
Helen
Quoting Helen M Hudson <[EMAIL PROTECTED]>:
So, if my table structure was:
id | date | order_ref | amount
1 | 1/1/01 | 100 | 1000 << these 2 are the rows
2 | 1/1/01 | 100 | 200 << i want to exclude
3 | 2/1/01 | 100 | 1000
4 | 2/1/01 | 100 | 200
5 | 2/1/01 | 100 | 50
I'd like to end up with
latest_date_on_order | order_ref | sum(amount)
2/1/01 | 100 | 1250
Not tested
SELECT MAX(Date), order_ref, SUM(amount) FROM table WHERE order_ref=100
GROUP BY
amount
That should sum all the amounts that is distinct, and have a order ref of
100.
I'm not sure if MAX(date) will be accepted - but there are better ways to
select the date depending on the column type..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
