Search code examples
mysqlsqldatabaseunique

Count unique occurrences per each distinct variable in MySQL

My table is structured like this:

Table name my_table

name shop_id
____ _______
Mark aaa
Mark aaa
Mark bbb
Mark ccc
Mark ddd
Mark ddd
Jane aaa
Jane aaa
Jane bbb
Cody aaa

I want to find:

  1. how many times a visitor went to stores in total
  2. how many unique shops did the visitor go to.

For question 1, I figured the code should be this:

SELECT 
  name,
  count(name) as name_count
FROM my_table
GROUP BY name
ORDER BY name_count DESC

But I can't figure the second part. Help please! It should be something like this:

name shop_count
____ _______
Mark 4
Jane 2
Cody 1

Thank you.

Solution

  • Use DISTINCT for unique shops

    SELECT 
  name,
  COUNT(name) as name_count
  COUNT(DISTINCT shop_id) AS unique_shop
FROM my_table
GROUP BY name
ORDER BY name_count DESC