User9123 User9123 - 1 year ago 53
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:


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

and another like this:


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, count(distinct installs.user)
from installs, apps
where =;

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

app1 4

Answer Source

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.

FROM apps A
INNER JOIN installs I ON =