SQL to get distinct statistics

Question

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!

Answer 1

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

Answer 2

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.