SQL AVG(COUNT(*))?

Question

I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it.

I have 3 tables a little like this

DVD

ID | NAME
1  | 1       
2  | 1     
3  | 2      
4  | 3

COPY 

ID | DVDID   
1  | 1  
2  | 1  
3  | 2  
4  | 3  
5  | 1

LOAN

ID | DVDID | COPYID  
1  | 1     |  1  
2  | 1     |  2  
3  | 2     |  3    
4  | 3     |  4  
5  | 1     |  5
6  | 1     |  5
7  | 1     |  5
8  | 1     |  2

etc

Basically, I'm trying to find all the copy ids that appear in the loan table LESS times than the average number of times for all copies of that DVD.

So in the example above, copy 5 of dvd 1 appears 3 times, copy 2 twice and copy 1 once so the average for that DVD is 2. I want to list all the copies of that (and each other) dvd that appear less than that number in the Loan table.

I hope that makes a bit more sense...

Thanks

Solution 1

Similar to dotjoe's solution, but using an analytic function to avoid the extra join. May be more or less efficient.

with 
loan_copy_total as 
(
    select dvdid, copyid, count(*) as cnt
    from loan
    group by dvdid, copyid
),
loan_copy_avg as
(
    select dvdid, copyid, cnt, avg(cnt) over (partition by dvdid) as copy_avg
    from loan_copy_total
)
select *
from loan_copy_avg lca
where cnt <= copy_avg;

Solution 2

This should work in Oracle:

create view dvd_count_view
select dvdid, count(1) as howmanytimes
  from loans
 group by dvdid;

select avg(howmanytimes) from dvd_count_view;

Solution 3

Untested...

with 
loan_copy_total as 
(
    select dvdid, copyid, count(*) as cnt
    from loan
    group by dvdid, copyid
),
loan_copy_avg as
(
    select dvdid, avg(cnt) as copy_avg
    from loan_copy_total
    group by dvdid
)

select lct.*, lca.copy_avg
from loan_copy_avg lca
inner join loan_copy_total lct on lca.dvdid = lct.dvdid
    and lct.cnt <= lca.copy_avg; 

Comments

[Joe Phillips]

I've been seeing this 'with' syntax lately. Is this standard SQL or is it in Oracle?

[Dave Costa]

It's part of the ANSI SQL standard