CODEWITHSUNDEEP
sqloracle-databaseaggregate-functions
Dan
Dan

Reputation:

SQL AVG(COUNT(*))?

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

Upvotes: 8

Views: 29107

Answers (3)

Dave Costa
Dave Costa

Reputation: 48121

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;

Upvotes: 6

dotjoe
dotjoe

Reputation: 26940

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;

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

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;

Upvotes: 3

Related Questions