CODEWITHSUNDEEP
sqldatabasesqlite
Anonymous
Anonymous

Reputation: 1373

Count unique rows

Im having this problem where im trying to count the number of entries by a unique value.

SELECT table1.PID, table2.CID

FROM table1 

INNER JOIN table2 using (OID)

WHERE table1.PID IN (
    SELECT table1.PID
    FROM table1 
    JOIN table2 using (OID)
    WHERE table2.CID = 'A'
) AND table2.CID != 'A'

what I would like to do is to count the number of unique table2.CID entries.

NOTE: I need to do grouping cause of duplicate values.

Just to help, here is a picture of the table i'm getting as an output. What I would like to have is the count of each name on unique ID values. So ERNSH should return 7 and not 15.

enter image description here

Upvotes: 3

Views: 89

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want one record per CID, so group by CID. Then use COUNT. And as you want to count distinct values use COUNT(DISTINCT):

SELECT COUNT(DISTINCT table1.PID), table2.CID
FROM table1 
INNER JOIN table2 using (OID)
WHERE table1.PID IN (
    SELECT table1.PID
    FROM table1 
    JOIN table2 using (OID)
    WHERE table2.CID = 'A'
) AND table2.CID != 'A'
GROUP BY table2.CID;

Upvotes: 2

Related Questions