CODEWITHSUNDEEP
sqlsql-server
tintanegra
tintanegra

Reputation: 9

How do I count distinct values from same id?

How do I count distinct values from a column if there are some duplicates for each ID?

I've tried COUNT, DISTINCT, CASE and everything but I can't count distinct values for each ID. I have a table like this.

ID          Symptom
1           Headache
1           Headache
1           Hematuria
1           Leg pain
1           Headache
2           Cough
2           Headache
2           Cough
3           Cough
3           Cough
3           Cough

I want to obtain something like this.

ID    Symptom 
1     Headache
1     Hematuria
1     Leg pain
2     Cough
2     Headache
3     Cough

Or how do I obtain the total count? Like there are 5 distinct symtomps but not 11 symptoms (If I use DISTINCT I would obtain 4).

Upvotes: 0

Views: 3041

Answers (3)

Fourat
Fourat

Reputation: 2447

Just group by those fields :

SELECT ID, Symptom
FROM Symptoms
GROUP BY ID, Symptom

Fiddle : http://sqlfiddle.com/#!18/48dde/2/0

Upvotes: 0

mosgu
mosgu

Reputation: 54

Try this:

SELECT ID, Symptom
FROM MY_TABLE
GROUP BY 1,2

Upvotes: -1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need distinct with select statement :

select distinct id, Symptom
from table t;

If you need unique count then use it inside count() :

select id, count(distinct Symptom)
from table t
group by id;

Upvotes: 4

Related Questions