SQL two "group by" in the same query

Question

We have the table to track what product page a user visited.

product_tracking
____________________
id  user_id  product
1   1        A
2   1        B
3   2        A 
4   1        A

I know we can use group by user_id, and group_by product but I need both.

Expected result :

result_table 
____________________________
user_id      A     B    C 
  1          2     1    0
  2          1     0    0

any idea how to merge the 2 group by ?

Maybe you should show *us* the "how" you want the data merged, so we can tell you if and how it can be done. What is the expected result set? — Frazz, May 12 '14 at 06:43
That is not a double group by, but something more like a pivot. Not all RDBMS support CROSSTAB selects. MySQL doesn't, AFAIK. You should look into http://stackoverflow.com/questions/7674786/mysql-pivot-table — Frazz, May 12 '14 at 06:48

Arion · Accepted Answer · 2014-05-12T06:52:12.770

2

You could do this:

SELECT
    product_tracking.user_id,
    SUM(CASE WHEN product_tracking.product='A' THEN 1 ELSE 0 END) AS A,
    SUM(CASE WHEN product_tracking.product='B' THEN 1 ELSE 0 END) AS B,
    SUM(CASE WHEN product_tracking.product='C' THEN 1 ELSE 0 END) AS C
FROM
    product_tracking
GROUP BY
    product_tracking.user_id

edited May 12 '14 at 06:52

answered May 12 '14 at 06:44

Arion

30,443
10
68
86

score 1 · Answer 2 · answered May 12 '14 at 07:00

An alternative to using sum(...) is using count(...):

SELECT product_tracking.user_id, COUNT(CASE WHEN product_tracking.product='A' THEN 1 END) AS A, COUNT(CASE WHEN product_tracking.product='B' THEN 1 END) AS B, COUNT(CASE WHEN product_tracking.product='C' THEN 1 END) AS C FROM product_tracking GROUP BY product_tracking.user_id

SQL two "group by" in the same query

2 Answers2