Multiple Aggregates and Group By

r270ba
r270ba used Ask the Experts™
on
I need to add a sum for the yearly quantity as well as the monthly quantity below.  Right now, I am just summing the quantity and group by month, year.  That is not working so any ideas?  What I want to see is another column QtyYear with a sum of all the months quantities.

*See query and results below.
==========Query=============
select item_number, sum(quantity) as QtyMonth, year, month from spvinventoryitemsummary
where item_number='5004pc' and month>='2009/01' and month<='2009/12'
group by item_number, year, month

=========Results========
item_number	QtyMonth	year	month
5004PC                         	301.0000000	2009	2009/01
5004PC                         	459.0000000	2009	2009/02
5004PC                         	154.0000000	2009	2009/03
5004PC                         	179.0000000	2009	2009/04
5004PC                         	224.0000000	2009	2009/05
5004PC                         	202.0000000	2009	2009/06
5004PC                         	192.0000000	2009	2009/07
5004PC                         	71.0000000	2009	2009/08
5004PC                         	307.0000000	2009	2009/09
5004PC                         	40.0000000	2009	2009/10
5004PC                         	80.0000000	2009	2009/11
5004PC                         	25.0000000	2009	2009/12

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi r270ba,

Will the QtyYear column show the same value for all records with same year but different months?

Try
select item_number, sum(quantity) as QtyMonth, year, month, QtyYear
from
  spvinventoryitemsummary A INNER JOIN
  (SELECT item_number as i, year as y, sum(quantity) as QtyYear
   from spvinventoryitemsummary
   GROUP BY item_number, year) B ON (A.item_number=B.i AND A.year=B.y)
where item_number='5004pc' and month>='2009/01' and month<='2009/12'
group by item_number, year, month

Open in new window

Author

Commented:
Yea I got an error running that but here is the corrected query.  That seems to do what I want.  Is there a way to divide the QtyMonth / QtyYear now?  I need to get the monthly % of qtys that make up the entire year.
select item_number, sum(quantity) as QtyMonth, year, month, QtyYear
from
  spvinventoryitemsummary A INNER JOIN
  (SELECT item_number as i, year as y, sum(quantity) as QtyYear
   from spvinventoryitemsummary
   GROUP BY item_number, year) B ON (A.item_number=B.i AND A.year=B.y)
where item_number='5004pc' and month>='2009/01' and month<='2009/12'
group by item_number, year, month, QtyYear

Open in new window

Yes, you can just divide the 2.
select item_number, sum(quantity) as QtyMonth, year, month, 100.0*sum(quantity)/QtyYear as Percentage
from
  spvinventoryitemsummary A INNER JOIN
  (SELECT item_number as i, year as y, sum(quantity) as QtyYear
   from spvinventoryitemsummary
   GROUP BY item_number, year) B ON (A.item_number=B.i AND A.year=B.y)
where item_number='5004pc' and month>='2009/01' and month<='2009/12'
group by item_number, year, month, QtyYear

Open in new window

Author

Commented:
I made one last modification.  Thanks for your help on the early Saturday morning!!!
select item_number, sum(quantity) as QtyMonth, year, month, QtyYear, 100.0*sum(quantity)/QtyYear as Percentage
from
  spvinventoryitemsummary A INNER JOIN
  (SELECT item_number as i, year as y, sum(quantity) as QtyYear
   from spvinventoryitemsummary
   GROUP BY item_number, year) B ON (A.item_number=B.i AND A.year=B.y)
where item_number='5004pc' and month>='2009/01' and month<='2009/12'
group by item_number, year, month, QtyYear

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial