CODEWITHSUNDEEP
sqlsql-server
Daniel Pereira
Daniel Pereira

Reputation: 1

SQL Query to get results from two conditions on column and a day on row

My table look like:

Date       | ID
-----------+-----------
2015-07-01 | 10
2015-07-01 | 10
2015-06-01 | 10
2015-07-01 | 10
2015-06-01 | 10
2015-07-01 | 10
2015-03-01 | 10
2015-02-01 | 10
2015-07-01 | 10
2015-07-01 | 10
2015-03-12 | 10
2015-02-09 | 10
2015-07-05 | 10
2015-07-03 | 10

And I need the Month in rows and day in column, and I need send as parameter the first and last month to get

The result that I need look like this it need to count how many occurrences on that day happen for the month.

      1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 
02    1  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  
03    1  0  0  0  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  
06    2  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  
07    6  0  1  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

Upvotes: 0

Views: 34

Answers (2)

sstan
sstan

Reputation: 36473

This is how you would do it using PIVOT:

select p.*
from (
  select day([Date]) as [Day],
         month([Date]) as [Month]
    from [Table]
) t
pivot (
  count([Day])
  for [Day] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
                [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
                [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) p
where p.[Month] between 1 and 12 -- adjust first and last month here.
order by p.[Month]

SQLFiddle Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is a pivot query. SQL Server has special syntax for this in the form of the pivot statement. I generally prefer to use conditional aggregation:

select month([date]) as mon,
       sum(case when day([date]) = 1 then 1 else 0 end) as day_01,
       sum(case when day([date]) = 2 then 1 else 0 end) as day_02,
       . . .
       sum(case when day([date]) = 31 then 1 else 0 end) as day_31
from table t
group by month([date]) 
order by mon;

If you are not using SQL Server, very similar syntax works in most databases, although the functions for day() and month() might differ.

Also, you might also need to include the year in the aggregation, if your data spans multiple calendar years.

Upvotes: 1

Related Questions