# Cumulative Count in SQL

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
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® is a registered trademark of EXPERTS EXCHANGE®

Commented:

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)
IT Engineer

Commented:

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
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.

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