CODEWITHSUNDEEP
mysqlsqlcount
Philip Trauring
Philip Trauring

Reputation: 33

SQL query to get number of times a field repeats for another specific field

Let's say I have two fields in a table. For the purpose of discussion let's say the fields are first name and last name. I want to know how many times the first name shows up listed next to the same last name. Assuming the last name column is unique, how do I figure out how many times each first name exists for each last name?

i.e. let's say I have a table with the following data:

Smith, John
Smith, David
Smith, Jane
Smith, John
Smith, John
Black, John
Black, Jane
Black, Jack
Black, Samantha
Black, Jack

I want a result that tells me that for Smith, there are 3 Johns, 1 David and 1 Jane, and for Black, there are 2 Jacks, 1 Jane and 1 Samantha.

Not sure how to format the output best. Maybe simply:

Smith, John, 3, David, 1, Jane, 1
Black, Jack, 2, Jane, 1, Samantha, 1

Something that would allow me to easily output something like:

Smith: John (3), David (1), Jane (1)
Black: Jack (2), Jane (1), Samantha (1)

It's important to note that it's possible that the second field can repeat, so counting unique instances in the column is not useful. It's only useful in reference to the first field.

Thanks.

Upvotes: 0

Views: 80

Answers (4)

dnoeth
dnoeth

Reputation: 60482

Simple aggregation?

select last_name, first_name, count(*)
from myTable
group by last_name, first_name
order by  last_name, first_name

Displaying it in a Smith, John, 3, David, 1, Jane, 1 format will probably fail because there are way too many Smiths with way too many different last names.

Upvotes: 0

SE1986
SE1986

Reputation: 2760

You can use a correlated sub-query in your SELECT statement and then wrap it all in a concat function to get the inline result you wanted

SELECT  DISTINCT
        CONCAT(LastName, ': ', FirstName, '(', (SELECT COUNT(FirstName) FROM Person WHERE FirstName = p.FirstName AND LastName = p.LastName),')')
FROM    Person p

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

SELECT CONCAT( Name,':', GROUP_CONCAT(CONCAT( LastName, ' (', CNT , ')' ), ' ') ) 
FROM (
  SELECT Name, LastName, COUNT(*) CNT FROM MyTable
  GROUP BY 
  Name, LastName
 ) T 
 GROUP BY Name

Sql Fiddle

Result:

Black:Jane (1) ,John (1) ,Samantha (1) ,Jack (2)
Smith:David (1) ,Jane (1) ,John (3)

Upvotes: 0

sorineatza
sorineatza

Reputation: 106

My solution:

select name,surname, count(surname) over (partition by name) as cnt
from your_table

Upvotes: 0

Related Questions