CODEWITHSUNDEEP
mysqlsqlgroup-by
Nabil Sham
Nabil Sham

Reputation: 2355

SQL two "group by" in the same query

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 ?

Upvotes: 0

Views: 124

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

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

Upvotes: 1

Arion
Arion

Reputation: 31249

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

Upvotes: 2

Related Questions