Reputation: 65
I would like to pivot and count the following data in Standard SQL (using Google Add-On OWOX BI with Big Query):
so that it outputs in standard sql as the below:
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
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
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 0
s instead of NULL
s, then add else 0
to the case expressions.
Upvotes: 0