MySQL: Group By Column And Include Its Count

Wave Crashing, MySQLWithout going into too much detail about the why, consider the below table. It’s a snippet of a table I’m working with for an e-commerce site. Any user (user_id) can have different products (product_id), but any user can also have multiple entries of the same product.

id user_id product_id
1 10 50
2 11 50
3 11 51
4 11 51
5 12 50
6 12 50

When displaying this data to a user, say user 11, I can’t do a simple flat list. This would confuse the user, showing multiple entries of the same product. Like this:

select id, user_id, product_id 
FROM wp_wc_licenses 
WHERE user_id = 11;

And the resulting table:

id user_id product_id
2 11 50
3 11 51
4 11 51

The solution
To give the user a more accurate display of their products, I want to group them by product, but also include a count of that product. Here is a much better way to get the data that makes sense.

SELECT id, user_id, product_id, count(*) AS `product_count`
FROM product
WHERE user_id = 11 group by product_id;

And the resulting table:

id user_id product_id product_count
2 11 50 1
2 11 51 2

I found this technique from this post. It saved me from myself, having to process this in PHP. That. Would. Have. Sucked.

Posted in: Code Samples, Development  |  Tagged with:  |  Leave a comment

Leave a Reply