CODEWITHSUNDEEP
sql
derekhh
derekhh

Reputation: 5552

How should we count the number of distinct pairs / tuples in SQL?

Suppose the schema is like this:

First Name | Last Name | Age | ID
====================================
John         Smith       18    123
John         Smith       21    234
John         Smith       19    123
Cathy        Zhang       20    144
Cathy        Zhang       20    144
Jackie       Chan        35    456

Suppose I want to count the number of distinct pairs after each (first name, last name). So that the output should be:

John         Smith       3
Cathy        Zhang       1
Jackie       Chan        1

I think I can first do:

SELECT FirstName,LastName,Age,ID,COUNT(*);

And then

SELECT FirstName,LastName,COUNT(*)

Is there a better approach?

Upvotes: 9

Views: 18509

Answers (3)

TechDo
TechDo

Reputation: 18659

Please try:

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) 
FROM (
        SELECT DISTINCT * FROM YourTable 
    ) X
GROUP BY FirstName, LastName

Upvotes: 2

sgeddes
sgeddes

Reputation: 62861

To return the count of each distinct firstname lastname, you would use COUNT and GROUP BY:

SELECT FirstName, LastName, COUNT(*)
FROM TableName
GROUP BY FirstName, LastName

In your above example, I think you meant Zhang to have a count of 2 though.

--EDIT

If I'm understanding your latest comment correctly, you want to also use DISTINCT:

SELECT FirstName, LastName, Age, Id, COUNT(*)
FROM (SELECT DISTINCT FirstName, LastName, Age, Id FROM TableName) T
GROUP BY FirstName, LastName, Age, Id

Good luck.

Upvotes: 13

Aditya Jain
Aditya Jain

Reputation: 1095

Try using,

SELECT firstName, lastName, count(1) FROM name_tab GROUP BY firstName, lastName;

Upvotes: 2

Related Questions