Can I use SQL (MySQL) to get result like sumif?

I know the usage of count and group by like "SELECT customer_id, COUNT(product_type) as sales_count FROM sales GROUP BY customer_id, product_type";
*******************
Result:
customer_id            sales_count
1001                  4
1002                  8
...

My question is: The value of product_type must be 1, 2, 3 or 4, can I use one sql to get count of each type?

I want result like:
customer_id            type_1      type_2      type_3      type_4
1001                  2            1            1            0
1002                  3            0            4            1
...
LVL 5
yauhingAsked:
Who is Participating?
 
snoyes_jwConnect With a Mentor Commented:
No need for the CASE - since the euqal operator returns 1 or 0, you can sum that directly:

SELECT
  customer_id,
  SUM(product_type = 1) AS type_1,
  SUM(product_type = 2) AS type_2...
0
 
lwadwellCommented:
Hi yauhing,

try using a CASE statement inside a SUM ...

SELECT customer_id, SUM(CASE when product_type = 1 then 1 else 0 end) as type_1
FROM sales
GROUP BY customer_id

lwadwell
0
 
yauhingAuthor Commented:
Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.