User9123 User9123 - 6 months ago 7
SQL Question

SQL Getting count of distinct rows for each value

I don't have much experience to SQL, and I can't seem to figure out this problem.

If I have a table like this:

Installs

id user
1 bob
2 carl
2 carl
2 charles
3 bill
3 bill


and another like this:

Apps

id name
1 app1
2 app2
3 app3


How would I get something like this?:

name distinct_users
app1 1
app2 2
app3 1


I have tried this:

select apps.name, count(distinct installs.user)
from installs, apps
where installs.id = apps.id;


but that only yields one row because it is counting the total number of distinct users in installs.

app1 4

Answer

You need to add the GROUP BY.
More over avoid comma separated table concepts and use JOIN with ON.
Add alias name for each table to the better readability.

SELECT A.name, COUNT(DISTINCT I.user) 
FROM apps A
INNER JOIN installs I ON I.id = A.id
GROUP BY A.name;
Comments