SQL to get distinct statistics
- by Sung Kim
Hi,
Suppose I have data in table X:
id assign team
----------------------
1 hunkim A
1 ygg A
2 hun B
2 gw B
2 david B
3 haha A
I want to know how many assigns for each id. I can get using:
select id, count(distinct assign) from
X group by id
order by count(distinct assign)desc;
It will give me something:
1 2
2 3
3 1
My question is how can I get the average of the all assign counts?
In addition, now I want to know the everage per team. So I want to get something like:
team assign_avg
-------------------
A 1.5
B 3
Thanks in advance!
