CODEWITHSUNDEEP
sqlgoogle-bigquery
user1283776
user1283776

Reputation: 21844

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?

Upvotes: 0

Views: 42

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

SELECT
  month_num,
  MIN(CASE WHEN [year] = '2014' THEN cnt END) AS year_2014,
  MIN(CASE WHEN [year] = '2015' THEN cnt END) AS year_2015,
  MIN(CASE WHEN [year] = '2016' THEN cnt END) AS year_2016
FROM (
  SELECT 
    STRFTIME_UTC_USEC(created_at, '%Y') AS [year], 
    STRFTIME_UTC_USEC(created_at, '%m') AS month_num,
    COUNT(*) AS cnt
  FROM zendesk.zendesk
  GROUP BY 1,2
)
GROUP BY 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 1

Related Questions