Reputation: 81
Sample dataset:
|ownerId|category|aggCategory1|aggCategory2|
--------------------------------------------
| 1 | dog | animal | dogs |
| 1 | puppy | animal | dogs |
| 2 | daisy | flower | ignore |
| 3 | rose | flower | ignore |
| 4 | cat | animal | cats |
...
Looking to do a group by that contains number of owners from category, aggCategory1, aggCategory2 for example outputting:
|# of owners|summaryCategory|
-----------------------------
| 1 | dog |
| 1 | puppy |
| 1 | daisy |
| 1 | rose |
| 1 | cat |
| 2 | animal |
| 2 | flower |
| 1 | dogs |
| 2 | ignore |
| 1 | cats |
Doesn't have to be that format but looking to get the above data points.
Thanks!
Upvotes: 2
Views: 1822
Reputation: 1269443
One method is to use union all
to unpivot the data and then aggregation in an outer query:
SELECT category, COUNT(*)
FROM (SELECT ownerID, category
FROM t
UNION ALL
SELECT ownerID, aggCategory1
FROM t
UNION ALL
SELECT ownerID, aggCategory2
FROM t
) t
GROUP BY category
The more BigQuery'ish way to write this uses arrays:
SELECT cat, COUNT(*)
FROM t CROSS JOIN
UNNEST(ARRAY[category, aggcategory1, aggcategory2]) cat
GROUP BY cat;
Upvotes: 1
Reputation: 32003
use union all
with cte as
(
SELECT ownerID, category as summaryCategory
FROM table
UNION
SELECT ownerID, aggCategory1 as summaryCategory
FROM table
UNION
SELECT ownerID, aggCategory2 as summaryCategory
FROM table
) select count(ownerID),summaryCategory from cte group by summaryCategory
Upvotes: 0
Reputation: 1305
SELECT COUNT(T.ownerID), T.category
FROM (
SELECT ownerID, category
FROM table
UNION
SELECT ownerID, aggCategory1
FROM table
UNION
SELECT ownerID, aggCategory2
FROM table
) AS T
GROUP BY T.category
With a GROUP BY
and the union with all of yours categories columns, it can be good.
Upvotes: 0