MySQL Forums
Forum List  »  Newbie

select for one and for all
Posted by: Stephen Morawski
Date: April 28, 2008 11:15AM

Trying to keep this simple,
I have many pickers and I am looking to find:
How many apples, how many oranges picked today and total of all fruit so far for each picker..

[picker] [apples] [oranges [date]
a 0 50 20080102
b 0 75 20080102
c 0 30 20080102
a 20 0 20080103
b 50 0 20080103
c 10 0 20080103
a 0 95 20080104
b 100 0 20080104
c 0 85 20080104

The results I am trying to get are something like what follows:
[picker] [apples] [oranges] [date] [total fruit]
a 0 95 20080104 165
b 100 0 20080104 225
c 0 85 20080104 125

I can can easily get the most current rows for each picker
select picker, apples, oranges from work where date='20080104',
I can easily get the total fruit for each picker
select picker, (sum(apples) + sum(oranges)) from work group by picker,
I just cannot seem to cram it all in one sql query.

I have looked at 'WITH ROLLUP' but it does not seem to do what I need to do.

Options: ReplyQuote

Written By
select for one and for all
April 28, 2008 11:15AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.