MSSQL Totals per day for a month

Question

I am trying to do something very similar to a question I have asked before but I cant seem to get it to work correctly. Here is my previous question: How to get totals per day

the table looks as follows:

              Table Name: Totals
Date       |Program label   |count
           |                |    
2013-04-09 |Salary Day      |4364
2013-04-09 |Monthly         |6231
2013-04-09 |Policy          |3523
2013-04-09 |Worst Record    |1423
2013-04-10 |Salary Day      |9872
2013-04-10 |Monthly         |6543
2013-04-10 |Policy          |5324
2013-04-10 |Worst Record    |5432
2013-04-10 |Salary Day      |1245
2013-04-10 |Monthly         |6345
2013-04-10 |Policy          |5431
2013-04-10 |Worst Record    |5232

My question is: Using MSSQL 2008 - Is there a way for me to get the total counts per Program Label per day for the current month. As you can see sometimes it will run twice a day. I need to be able to account for this.

The output should look as follows:

Date      |Salary Day |Monthly |Policy |Worst Record
2013-04-9 |23456      |63241   |23521  |23524
2013-04-10|45321      |72535   |12435  |83612

score 8 · Answer 1 · answered Jul 04 '13 at 07:52

Use the PIVOT table operator like this:

SELECT  *
FROM Totals AS t
PIVOT
(
   SUM(count)
   FOR [Program label] IN ([Salary Day], 
                           [Monthly], 
                           [Policy], 
                           [Worst Record])
) AS p;

See it in action:

SQL Fiddle Demo

This will give you:

|       DATE | SALARY DAY | MONTHLY | POLICY | WORST RECORD |
-------------------------------------------------------------
| 2013-04-09 |       4364 |    6231 |   3523 |         1423 |
| 2013-04-10 |      11117 |   12888 |  10755 |        10664 |

Thanks. I have learned something new through this :) Really helpful answer. I have not chosen to use it as its not something I am completely comfortable with yet. Although I will definitely start testing this out with my future queries. — DeanMWake, Jul 04 '13 at 09:54

score 3 · Accepted Answer · answered Jul 04 '13 at 07:59

3

Try this

select Date,
  sum(case when [Program label] = 'Salary Day' then count else 0 end) [Salary Day],
  sum(case when [Program label] = 'Monthly' then count else 0 end) [Monthly],
  sum(case when [Program label] = 'Policy' then  count else 0 end) [Policy],
  sum(case when [Program label] = 'Worst Record' then count else 0 end) [Worst Record]
from Totals Group by [Date];

answered Jul 04 '13 at 07:59

Amit

15,217
8
46
68

I have used your answer as it is the simplest, for me, to understand even though I have had to edit it a lot. Thanks. :) – DeanMWake Jul 04 '13 at 09:52

score 2 · Answer 3 · answered Jul 04 '13 at 07:53

Try this one -

SELECT 
    date
    , [Salary Day] = SUM(CASE when [Program label] = 'Salary Day' then COUNT end)
    , [Monthly] = SUM(CASE when [Program label] = 'Monthly' then COUNT end) 
    , [Policy] = SUM(CASE when [Program label] = 'Policy' then COUNT end) 
    , [Worst Record] = SUM(CASE when [Program label] = 'Worst Record' then COUNT end)  
FROM Totals
GROUP BY [Date];

MSSQL Totals per day for a month

3 Answers3