cwight
cwight

Reputation: 65

How can I PIVOT and COUNT in Standard SQL

I would like to pivot and count the following data in Standard SQL (using Google Add-On OWOX BI with Big Query):

INPUT

so that it outputs in standard sql as the below:

RESULTS

So I would want to select the itemsku and pivot based off of the "Reason."

Can't figure this out, thank you!!!

Upvotes: 2

Views: 274

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL

#standardSQL
SELECT itemsku,
       COUNTIF(reason = 'BIG') AS big,
       COUNTIF(reason = 'NONE') AS none,
       COUNTIF(reason = 'SMALL') AS small
FROM `project.dataset.table`
GROUP BY itemsku

You can test, play with above using dummy data from your question as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1010101 itemsku, 'BIG' reason UNION ALL
  SELECT 1010101, 'BIG' UNION ALL
  SELECT 13333, 'NONE' UNION ALL
  SELECT 13333, 'NONE' UNION ALL
  SELECT 14444, 'NONE' UNION ALL
  SELECT 14444, 'NONE' UNION ALL
  SELECT 14444, 'SMALL' 
)
SELECT itemsku,
       COUNTIF(reason = 'BIG') AS big,
       COUNTIF(reason = 'NONE') AS none,
       COUNTIF(reason = 'SMALL') AS small
FROM `project.dataset.table`
GROUP BY itemsku
-- ORDER BY itemsku     

with result as

Row     itemsku     big     none    small    
1       13333       0       2       0    
2       14444       0       2       1    
3       1010101     2       0       0    

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can use conditional aggregation:

select itemsku,
       sum(case when reason = 'BIG' then 1 end) as big,
       sum(case when reason = 'SMALL' then 1 end) as small,
       sum(case when reason = 'NONE' then 1 end) as none
from t
group by itemsku;

If you want 0s instead of NULLs, then add else 0 to the case expressions.

Upvotes: 0

Related Questions