SQL to get distinct statistics
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:
tea开发者_运维技巧m assign_avg
-------------------
A 1.5
B 3
Thanks in advance!
SELECT
AVG(CAST(assign_count AS DECIMAL(10, 4)))
FROM
(SELECT
id,
COUNT(DISTINCT assign) AS assign_count
FROM
X
GROUP BY
id) Assign_Counts
.
SELECT
team,
AVG(CAST(assign_count AS DECIMAL(10, 4)))
FROM
(SELECT
id,
team,
COUNT(DISTINCT assign) AS assign_count
FROM
X
GROUP BY
id,
team) Assign_Counts
GROUP BY
Team
What you want can be done in one query, using aggregate functions COUNT and AVG:
SELECT t.id,
COUNT(*) AS num_instances,
AVG(t.id) AS assign_avg
FROM TABLE t
GROUP BY t.id
Columns that do not have an aggregate function performed on them need to be defined in the GROUP BY clause.
精彩评论