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

Most recent work

  • Our Restroom Our Restroom is an (inter)national crowdsourced campaign who’s goal is simple: compel businesses to make their single occupancy restrooms gender neutral. Championed by Kristin Russo, designed by Allison Weiss, built by me on WordPress.
  • How To Win At Feminism The badasses over at Reductress are at it again with their new book How To Win At Feminism.
  • Reductress I crank out on-going web projects for Reductress. Super fun as you can imagine.
  • Everyone Is Gay Everyone Is Gay is an indispensable question and answer resource for the LGBTQ community. Hell, it’s great advice for anyone with a body with an emotion or two. I made the site responsive among other things. Give it a look see.

Need hosting?

  • Media Temple My go to hosting provider. You get rockstar hosting and I make a little cash. No pressure, just clean honest fun.

Past work

  • Christine Chaney Creative The online presence of Seattle artist Christine Chaney. Specializing in architecture, art and apparel.
  • Get Your Shit Together This is a site dedicated to helping you get your shit together before an unexpected tragedy, like the loss of a loved one.
  • IHG Instagram Sweepstakes An awesome Instagram driven sweepstakes site for IHG.
  • Trivial Beersuit A site that provides professional pub trivia to bars and restaurants.
  • Audi YouTube Channel This site was a quick turn-around hair pulling adventure for the Super Bowl: revamping Audi’s YouTube Channel.
  • The new Providing tools to help students become teachers.
  • Valhalla DSP A one man Seattle based company, writing pretty sick professional audio plugins. Check ’em out!
  • Microsoft Visual Studio 2012 Launch This sweet new Visual Studio 2012 Launch website sports a mobile first, responsive web design architecture and comes in 14 different languages.
  • Nice Manners Music Nice Manners is an LA based recording studio offering their clients all sorts of digital audio services. This is a demo of an internal genre based music search & player I built for them. I am ecstatic about launching this project—check it out.
  • Holiday Inn Tumblr A quick, fully responsive update to the Holiday Inn Tumblr page.
  • Microsoft's Art of Touch An artful HTML5 Microsoft website. Sadly, this site is no longer live.
  • Microsoft Hardware Healthy Computing Tool A useful little tool to help you find the perfect Microsoft mouse or keyboard.
  • Pella Professional Pella manufactures high quality doors and windows. This is their professional site.
  • Imaging the World A non-profit dedicated to providing ultrasound services in remote areas of the world.
  • Ginny Ruffner: A not so still life A site for a documentary film about Seattle artist Ginny Ruffner.