CODEWITHSUNDEEP
mysqlsql
Undefined
Undefined

Reputation: 1929

SQL count on multiple columns?

If I have two columns in my table say, first_name and last_name and I want to find out how many people share the same name for example:

Name Count | Name
-------------------------
 12        | John Smith
  8        | Bill Gates
  4        | Steve Jobs

Upvotes: 0

Views: 984

Answers (3)

Anton
Anton

Reputation: 4018

Since names can have different capitalizations (i.e. 'John' and 'john'), and possibly excess spaces in the database, first use a subquery that cleans up and concatenates the first and last names, and then use COUNT and GROUP BY:

SELECT COUNT(*) AS `name_count`
FROM (
    SELECT CONCAT(LOWER(TRIM(`first_name`)), ' ', LOWER(TRIM(`last_name`))) AS `full_name`
    FROM `table`
) AS `table_with_concat_names`
GROUP BY `full_name`
ORDER BY `name_count` DESC;

You'll notice I applied LOWER(TRIM()) to both the first and last names. This way, they are made all lowercase with LOWER() so that 'John Smith' and 'john smith' are the same person when compared, and also I used TRIM() to remove excess spaces, so 'John Smith ' (space after) and 'John Smith' are the same person too.

Upvotes: 1

user359040
user359040

Reputation:

Group by both columns - eg:

select firstname, lastname, count(*) as `Name Count`
from table
group by firstname, lastname

Upvotes: 3

ajp
ajp

Reputation: 1490

Use a group by clause 

select (firstname + ' ' + lastname) as Name, count(*) as 'Name Count'
from table
group by (firstname + lastname)

Upvotes: 0

Related Questions