Suppose I have the following table "Person":
| id | name | friends |
|---|---|---|
| 1 | matt | jim |
| 1 | matt | ray |
| 1 | matt | ray |
| 2 | tim | fry |
| 3 | sally | jack |
| 3 | sally | tim |
| 4 | matt | harold |
I want to output the following:
1 matt 2
2 tim 1
3 sally 2
4 matt 1
*Note that entries can have duplicates, so for example we see "1 matt 2" instead of "1 matt 3", but name is not unique, so since there are two matts with different ids, we also see "4 matt 1".
I've tried the following:
SELECT id, name, COUNT(distinct friends)
FROM Person;
But COUNT doesn't work how I had expected in this scenario. Is there a way to get my expected output?
CodePudding user response：
- Tested on dbfiddle, try this:
SELECT id, name, COUNT(distinct friends)
FROM Person
GROUP BY id, name;
CodePudding user response：
you should make a group by the id and the name