MySQL Count Column

Question

I have the following db table, and I would like to be able to count amount of each product_id per name.

---------------------
| name | product_id |
---------------------
| David |      1    |
|Charlie|      1    |
| David |      2    |
| David |      1    |
|Charlie|      2    |
|Charlie|      3    |   
|Charlie|      2    |
|Charlie|      3    |
---------------------

I would like to able to create a result set like the following;

----------------------------------------------------------------------------
| name   | count(product_id_1) | count(product_id_1) | count(product_id_1) |
----------------------------------------------------------------------------
| David  |          2          |           1         |           0         |
|Charlie |          1          |           2         |           2         |
----------------------------------------------------------------------------

So, please help me how to query for the above problem, Thank's

give SUM a shot, and when it doesnt work, come back and post what you tried, good luck. https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html — Andrew, Oct 06 '17 at 02:57

score 0 · Accepted Answer · answered Oct 06 '17 at 02:59

If you wan't to group the count of products based on customers you can do something like the following if you are joining tables.

SELECT NAME, COUNT(product_id1), COUNT(product_id2), COUNT(product_id2) FROM Customers c INNER JOIN Ordered_Products ON c.id = customer.id GROUP BY c.name

otherwise if you're not doing a join you can do the following

SELECT NAME, COUNT(product_id1), COUNT(product_id2), COUNT(product_id2) FROM Customers c GROUP BY c.name

Seems like the question was tweaked a bit. I would go with what [fyrye](https://stackoverflow.com/users/1144627/fyrye) provided if that's what you're looking for. — dreamquezt, Oct 06 '17 at 03:01

MySQL Count Column

1 Answers1