SQL query to count incidents by year by month

Question

I have one table called Incidents. I want a SQL query that returns the count of incidents split out by year, by month. The start year is 2010, however, the end year will be variable.

Example Incidents table:

DateLogged    IncidentRef
-----------   ------------
2015-04-05    1
2014-06-04    2
2013-01-01    3
2012-12-10    4
2011-10-15    5
2010-10-01    6
2012-12-11    7
2011-10-10    8
2010-10-10    9

Query Returns:

Year Jan Fev Mar Abr Mai Jun Jul Ago Set Out Nov Dez
2010  0   0   0   0   0   0   0   0   0   2   0   0
2011  0   0   0   0   0   0   0   0   0   2   0   0
2012  0   0   0   0   0   0   0   0   0   0   0   2
2013  1   0   0   0   0   0   0   0   0   0   0   0
2014  0   0   0   0   0   1   0   0   0   0   0   0
2015  0   0   0   1   0   0   0   0   0   0   0   0

How can I do that query? Where do I start?

Possible duplicate of [Understanding PIVOT function in T-SQL](http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql) — Juan Carlos Oropeza, Oct 27 '15 at 19:15
Ok guys, sorry for that, it was not my intention duplicate questions. — Eduardo Arruda Pimentel, Oct 27 '15 at 19:23
Juan, in my opinion is not duplicated because using PIVOT is one of the solutions. — Eduardo Arruda Pimentel, Oct 27 '15 at 19:50

score 5 · Accepted Answer · answered Oct 27 '15 at 19:08

5

One option is to use conditional aggregation:

select year(datelogged), 
   sum(case when month(datelogged) = 1 then 1 else 0 end) Jan,
   sum(case when month(datelogged) = 2 then 1 else 0 end) Feb,
   ...,
   sum(case when month(datelogged) = 12 then 1 else 0 end) Dec
from yourtable
group by year(datelogged)

SQL Fiddle Demo

answered Oct 27 '15 at 19:08

sgeddes

62,311
6
61
83

Thank you, I will see an example, test and come back soon. – Eduardo Arruda Pimentel Oct 27 '15 at 19:17
Man, thank you very much. Nice and clean. It works and it's awesome. – Eduardo Arruda Pimentel Oct 27 '15 at 19:22
1

This might have an issue if there is a year without data -- it won't show up. – Hogan Oct 27 '15 at 19:27
1

@EduardoArrudaPimentel -- no problem, glad it helped. Pivoting is another good option, I just find this easier to read/understand for such a task (albeit a bit longer). – sgeddes Oct 27 '15 at 19:28
1

@Hogan -- yep, if you need a list of years, you'd want to create a year table and join against it -- up to the OP at that point. Thanks! – sgeddes Oct 27 '15 at 19:29
Marked this question as solved, because: is different than using PIVOT and may be usefull to anothers. And because it was the first answer, and it works. – Eduardo Arruda Pimentel Oct 27 '15 at 19:47

score 3 · Answer 2 · answered Oct 27 '15 at 19:16

3

With pivoting:

;with cte as(select year(date) y, month(date) m, ref from table)
select * from cte
pivot(count(ref) for m in([1],[2],...,[12]))p

answered Oct 27 '15 at 19:16

Giorgi Nakeuri

35,155
8
47
75

Thank you for your help! – Eduardo Arruda Pimentel Oct 27 '15 at 19:24
2

What about years with no data -- will those rows show up with 0s? – Hogan Oct 27 '15 at 19:29
1

@Hogan, there can't be years with no data. I think you meant months? – Giorgi Nakeuri Oct 27 '15 at 19:31
1

nope I mean years... think about it, for example 2016. Add 2016 to my code and it will show a row with zeros. You must of had this as a requirement at some point. – Hogan Oct 27 '15 at 19:33
1

@Hogan, for missing year for example 2020 there simply will not be row. Hogan, unless you use left join in your answer you will not show 2016 :) – Giorgi Nakeuri Oct 27 '15 at 19:34
1

yes this is my point.. often (every time for me) there is a requirement to show the rows with no data when doing a pivot. I am sure this has happened to you. – Hogan Oct 27 '15 at 19:36

Hogan · Answer 3 · 2015-10-27T19:38:23.250

1

Another way to do without a pivot you could use a join:

with years as
(
   SELECT 2010 as y
     UNION ALL
   SELECT 2011
     UNION ALL
   SELECT 2012
     UNION ALL
   SELECT 2013
     UNION ALL
   SELECT 2014
     UNION ALL
   SELECT 2015
)
select years.y, 
  sum(case when month(DateLogged) = 1 then 1 else 0) as jan,
  sum(case when month(DateLogged) = 2 then 1 else 0) as feb,
  sum(case when month(DateLogged) = 3 then 1 else 0) as mar,
  sum(case when month(DateLogged) = 4 then 1 else 0) as apr,
  -- ...
  sum(case when month(DateLogged) = 12 then 1 else 0) as dec,

from years y
left join incidents i on y.y = year(i.DateLogged)
group by y.DateLogged

If you want year to be "dynamic" you have the CTE like this

with years as
(
   SELECT DISTINCT year(i.DateLogged) FROM incidents 
)

but this has the same drawback as the sgeddes solution -- years with no values don't show up.

edited Oct 27 '15 at 19:38

answered Oct 27 '15 at 19:12

Hogan

69,564
10
76
117

Thank you so much for your help. Year starts on 2010 but could end on 2045. – Eduardo Arruda Pimentel Oct 27 '15 at 19:17
1

There should be left join I believe. – Giorgi Nakeuri Oct 27 '15 at 19:37
1

@Hogan, thank you again. Your question solve my problem too. There's no chance of years with no values(in my case). – Eduardo Arruda Pimentel Oct 27 '15 at 19:45

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

Using classic PIVOT:

Data:

CREATE TABLE #Incidents(
   DateLogged  DATE  NOT NULL PRIMARY KEY
  ,IncidentRef INTEGER  NOT NULL );
INSERT INTO #Incidents(DateLogged,IncidentRef) VALUES 
    ('2015-04-05',1),('2014-06-04',2),('2013-01-01',3),
    ('2012-12-10',4),('2011-10-15',5),('2010-10-01',6),
    ('2012-12-11',7),('2011-10-10',8),('2010-10-10',9);

Query:

;WITH cte AS
(
  SELECT [year] = YEAR(DateLogged),
         [month] = CASE MONTH(DateLogged)
                      WHEN 1 THEN 'Jan'  WHEN 2 THEN 'Fev'
                      WHEN 3 THEN 'Mar'  WHEN 4 THEN 'Abr'
                      WHEN 5 THEN 'Mai'  WHEN 6 THEN 'Jun'
                      WHEN 7 THEN 'Jul'  WHEN 8 THEN 'Ago'
                      WHEN 9 THEN 'Set'  WHEN 10 THEN 'Out'
                      WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dez'
                    END,
         IncidentRef
  FROM #Incidents  
)
SELECT [Year],Jan, Fev, Mar, Abr, Mai, Jun, Jul, Ago, [Set], Out, Nov, Dez
FROM cte
PIVOT (
  COUNT(IncidentRef)
  FOR [month]  IN (Jan, Fev, Mar, Abr, Mai, Jun,Jul, Ago, [Set], Out, Nov,Dez)
) AS piv;

LiveDemo

SQL Server 2012+

Using CHOOSE added UNION to cte to ensure to get zeros for missing years:

;WITH cte AS
(
  SELECT [year] = YEAR(DateLogged),
         [month] = CHOOSE(MONTH(DateLogged),'Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez'),       
         IncidentRef
  FROM #Incidents
  UNION ALL
  SELECT [year], NULL, NULL
  FROM (VALUES (2010),(2011),(2012),(2013),(2014),(2015)) AS t([year])
)
SELECT [Year],Jan, Fev, Mar, Abr, Mai, Jun, Jul, Ago, [Set], Out, Nov, Dez
FROM cte
PIVOT (
  COUNT(IncidentRef)
  FOR [month]  IN (Jan, Fev, Mar, Abr, Mai, Jun,Jul, Ago, [Set], Out, Nov,Dez)
) AS piv
ORDER BY [Year];

LiveDemo2

Thank you very much. Very nice solution too. – Eduardo Arruda Pimentel Oct 27 '15 at 19:44 — Eduardo Arruda Pimentel, Oct 27 '15 at 19:44

SQL query to count incidents by year by month

4 Answers4

SQL Server 2012+