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 <helen@stripped>:
>
>> 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..
>
