User9123 User9123 - 2 years ago 66
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 =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download