Geek asks and answers

How should I count the number of different pairs / tuples in SQL?

Assume the circuit is as follows:

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 individual pairs after each (first name, last name). So the conclusion should be:

John         Smith       3
Cathy        Zhang       1
Jackie       Chan        1

I think I can do it first:

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

And then

SELECT FirstName,LastName,COUNT(*)

Is there a better approach?

+5
source share
3 answers

To return an invoice for each individual surname name, you must use COUNTand GROUP BY:

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

In the above example, I think you meant that Zhang had a quantity of 2, though.

- EDIT

If I understand your last comment correctly, you also want to 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.

+7
source

Try:

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

Try using

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

More articles:


All Articles