user8766186
user8766186

Reputation: 81

BigQuery Standard SQL Group by aggregate multiple columns

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

Answers (3)

Gordon Linoff
Gordon Linoff

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

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

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

Arnaud Peralta
Arnaud Peralta

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

Related Questions