'How to get distinct value and specific count from a query

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?

mysqlsqlcountdistinct


Solution 1:[1]

you should make a group by the id and the name

Solution 2:[2]

  1. Tested on dbfiddle, try this:
SELECT id, name, COUNT(distinct friends)
FROM Person
GROUP BY id, name;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Aziz Yogo Utomo
Solution 2

Related Questions