user1283776 user1283776 - 6 months ago 11
SQL Question

Pivot and get count of rows in each cell?

The following query gives me the

year
and
month_num
of each support ticket.

SELECT STRFTIME_UTC_USEC(created_at, '%Y') AS year,
STRFTIME_UTC_USEC(created_at, '%m') AS month_num
FROM zendesk.zendesk


I want to pivot the year values and show the
COUNT(*)
of all source rows in each cell, like this:

2014 2015 2016
01 5 ... ...
02 8
03 12
04 22
05 30
06 15
07 10
08 9
09 ...
10
11
12


How can I do this?

Answer

You can use conditional aggregation:

SELECT STRFTIME_UTC_USEC(created_at, '%m') AS month_num,
       SUM(CASE WHEN STRFTIME_UTC_USEC(created_at, '%Y') = '2014' then 1 else 0 end) as cnt_2014,
       SUM(CASE WHEN STRFTIME_UTC_USEC(created_at, '%Y') = '2015' then 1 else 0 end) as cnt_2015,
       SUM(CASE WHEN STRFTIME_UTC_USEC(created_at, '%Y') = '2016' then 1 else 0 end) as cnt_2016
FROM zendesk.zendesk
GROUP BY month_num;
Comments