Cumulative Count in SQL

Graham Hirst
Graham Hirst used Ask the Experts™
on
Im trying to create a DB View that will provide me a cumulative count of work orders by Month for any given Priority;

How can I change the following statement;

SELECT TA_PRIORITY, COUNT(*) AS Tasks, MONTH(TA_EST_DATE) AS Months
FROM F_TASKS
WHERE YEAR(TA_EST_DATE) = 2009
GROUP BY TA_PRIORITY, MONTH(TA_EST_DATE)
ORDER BY TA_PRIORITY Asc, MONTH(TA_EST_DATE)

that is supplying me, or should supply me :-)

             Jan  Feb      Mar      Apr      Ect  Dec
Priority 1      34      87      45      54            
Priority 2      42      67      32      67            
Priority 3      56      45      78      43            
Priority 4      65      45      54      54            
Priority 5      34      43      76      45            

into the following;

             Jan      Feb      Mar      Apr      Ect      Dec
Priority 1      34      121      166      220            
Priority 2      42      109      141      208            
Priority 3      56      101      179      222            
Priority 4      65      110      164      218            
Priority 5      34      77      153      198            

any help would be much appreciated
Comment
Watch Question

Do more with

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

Commented:
SELECT TA_PRIORITY,
sum(case when MONTH(TA_EST_DATE)=1 then 1 else 0) Jan,
sum(case when MONTH(TA_EST_DATE)=2 then 1 else 0) Feb,
sum(case when MONTH(TA_EST_DATE)=3 then 1 else 0) Mar,
sum(case when MONTH(TA_EST_DATE)=4 then 1 else 0) Apr,
sum(case when MONTH(TA_EST_DATE)=5 then 1 else 0) May,
sum(case when MONTH(TA_EST_DATE)=6 then 1 else 0) Jun,
sum(case when MONTH(TA_EST_DATE)=7 then 1 else 0) Jul,
sum(case when MONTH(TA_EST_DATE)=8 then 1 else 0) Aug,
sum(case when MONTH(TA_EST_DATE)=9 then 1 else 0) Sep,
sum(case when MONTH(TA_EST_DATE)=10 then 1 else 0)Oct,
sum(case when MONTH(TA_EST_DATE)=11 then 1 else 0)Nov,
sum(case when MONTH(TA_EST_DATE)=12 then 1 else 0)Dec
FROM F_TASKS WHERE YEAR(TA_EST_DATE) = 2009
GROUP BY TA_PRIORITY
ORDER BY TA_PRIORITY Asc, MONTH(TA_EST_DATE)
Graham HirstIT Engineer

Author

Commented:
Thank you guys for replying.

shickasi, i followed your link and came up with the following;

SELECT TA_PRIORITY as Priority, COUNT(*) AS Tasks,
Cumulative = CASE WHEN TA_PRIORITY = TA_PRIORITY THEN (SELECT SUM(TA_SEQ) from F_TASKS a where a.TA_PRIORITY = F_TASKS.TA_PRIORITY and MONTH(a.TA_EST_DATE) <= MONTH(F_TASKS.TA_EST_DATE)) END,
MONTH(TA_EST_DATE) AS Months
FROM F_TASKS
WHERE YEAR(TA_EST_DATE) = 2008
GROUP BY TA_PRIORITY, MONTH(TA_EST_DATE)
ORDER BY TA_PRIORITY Asc, MONTH(TA_EST_DATE)

there is only  one prblem, the TA_SEQ value should be the count(*) and when i try and use the count(*) its not working, any ideas?

Vinurajr, I like what you did with the query and would like to change the above into what you recomended. the ponly problem is, yours is doing the count but not the cumulative count.

Any assistance would be much appreciated.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Commented:

SELECT TA_PRIORITY,
sum(case when MONTH(TA_EST_DATEend)=1 then 1 else 0 endend) Jan,
sum(case when MONTH(TA_EST_DATEend)=2 then 1 else 0 end) Feb,
sum(case when MONTH(TA_EST_DATEend)=3 then 1 else 0 end) Mar,
sum(case when MONTH(TA_EST_DATEend)=4 then 1 else 0 end) Apr,
sum(case when MONTH(TA_EST_DATEend)=5 then 1 else 0 end) May,
sum(case when MONTH(TA_EST_DATEend)=6 then 1 else 0 end) Jun,
sum(case when MONTH(TA_EST_DATEend)=7 then 1 else 0 end) Jul,
sum(case when MONTH(TA_EST_DATEend)=8 then 1 else 0 end) Aug,
sum(case when MONTH(TA_EST_DATEend)=9 then 1 else 0 end) Sep,
sum(case when MONTH(TA_EST_DATEend)=10 then 1 else 0 end)Oct,
sum(case when MONTH(TA_EST_DATEend)=11 then 1 else 0 end)Nov,
sum(case when MONTH(TA_EST_DATEend)=12 then 1 else 0 end)Dec
FROM F_TASKS WHERE YEAR(TA_EST_DATEend) = 2009
GROUP BY TA_PRIORITY
ORDER BY TA_PRIORITY Asc, MONTH(TA_EST_DATEend)

I missed End
Please try this query Hope it will give desired Result...

Commented:
can u please try this query....

SELECT TA_PRIORITY,
sum(case when MONTH(TA_EST_DATEend)=1 then 1 else 0 endend) Jan,
sum(case when MONTH(TA_EST_DATEend)=2 then 1 else 0 end) Feb,
sum(case when MONTH(TA_EST_DATEend)=3 then 1 else 0 end) Mar,
sum(case when MONTH(TA_EST_DATEend)=4 then 1 else 0 end) Apr,
sum(case when MONTH(TA_EST_DATEend)=5 then 1 else 0 end) May,
sum(case when MONTH(TA_EST_DATEend)=6 then 1 else 0 end) Jun,
sum(case when MONTH(TA_EST_DATEend)=7 then 1 else 0 end) Jul,
sum(case when MONTH(TA_EST_DATEend)=8 then 1 else 0 end) Aug,
sum(case when MONTH(TA_EST_DATEend)=9 then 1 else 0 end) Sep,
sum(case when MONTH(TA_EST_DATEend)=10 then 1 else 0 end)Oct,
sum(case when MONTH(TA_EST_DATEend)=11 then 1 else 0 end)Nov,
sum(case when MONTH(TA_EST_DATEend)=12 then 1 else 0 end)Dec
FROM F_TASKS WHERE YEAR(TA_EST_DATEend) = 2009
GROUP BY TA_PRIORITY
ORDER BY TA_PRIORITY Asc

Commented:

This will help ur Need I think:

SELECT TA_PRIORITY,
sum(case when MONTH(TA_EST_DATEend)=1 then 1 else 0 endend) Jan,
sum(case when MONTH(TA_EST_DATEend)in(1,2) then 1 else 0 end) Feb,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3) then 1 else 0 end) Mar,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4) then 1 else 0 end) Apr,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5) then 1 else 0 end) May,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6) then 1 else 0 end) Jun,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7) then 1 else 0 end) Jul,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7,8) then 1 else 0 end) Aug,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7,8,9) then 1 else 0 end) Sep,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7,8,9,10) then 1 else 0 end)Oct,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7,8,9,10,11) then 1 else 0 end)Nov,
sum(case when MONTH(TA_EST_DATEend)in(1,2,3,4,5,6,7,8,9,10,11,12) then 1 else 0 end)Dec
FROM F_TASKS WHERE YEAR(TA_EST_DATEend) = 2009
GROUP BY TA_PRIORITY
ORDER BY TA_PRIORITY Asc

Commented:
Hope this is working...?
Commented:
SELECT TA_PRIORITY as Priority, COUNT(*) AS Tasks,
Cumulative = sum(CASE WHEN TA_PRIORITY = TA_PRIORITY
THEN (SELECT count(*) from F_TASKS a where a.TA_PRIORITY = F_TASKS.TA_PRIORITY and MONTH(a.TA_EST_DATE) <= MONTH(F_TASKS.TA_EST_DATE))) END,
MONTH(TA_EST_DATE) AS Months
FROM F_TASKS
WHERE YEAR(TA_EST_DATE) = 2008
GROUP BY TA_PRIORITY, MONTH(TA_EST_DATE)
ORDER BY TA_PRIORITY Asc, MONTH(TA_EST_DATE)
Graham HirstIT Engineer

Author

Commented:
this is really impressive thank you guys

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