Count of each unique record in a DISTINCT query

Valkrider · December 21, 2018, 8:22pm

This is a Wordpress database question so I am not including my code here so am just asking a general How To / Is It Possible question.

I have a DISTINCT query that works fine that returns all the unique surnames in a table. What I would like is a count of each of those unique surnames from that database as well. Is this easily possible?

So what I would like is something like:
Jones (98)
Brown (50)
etc

astonecipher · December 21, 2018, 8:28pm

Something like,

select 
  surname,
  COUNT(surname) as total
 FROM 
   member
GROUP BY surname

Valkrider · December 21, 2018, 8:47pm

Thanks, I tried it in PHPMyAdmin and it throws an error

#1728 - Cannot load from mysql.proc. The table is probably corrupted

However the table is not corrupted and reads fine and runs any other query without issue

Valkrider · December 21, 2018, 9:20pm

I have done some more searching trying different parameters and have found this that works.

SELECT Name, COUNT(*) totalCount FROM tableName GROUP BY Name