asked on

Multiple Aggregates and Group By

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

Thomasian

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

r270ba

ASKER

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

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

r270ba

ASKER

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

r270ba

ASKER

Thanks