sql - Average with group by -
trying calculate average number of books borrowed per member.
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)
bold - primary key
italic - foreign key
can here me out? in advance
to number of distinct books borrowed each member, do
select membno, count(*) totalborrowed borrowed group membno
to total members, do
select count(distinct membno) totalmembers borrowed
to average books borrowed, should combine results. multiply 1.0
result in decimal.
select 1.0 * sum(totalborrowed)/count (distinct b.membno) avg_per_member (select membno, count(*) totalborrowed borrowed group membno) t join borrowed b on t.membno = b.membno
Comments
Post a Comment