How to get the max count of an attribute with 3 tables?-CodePudding

I need to query which author sold the most books and how many books the author sold.

select a.firstname ||''|| a.lastname as fullname,
       max(count(datesold))
from   author a,
       transaction t,
       book b
where  a.authorid = b.authorid
and    b.bookid = t.bookid
group by
       a.firstname,
       a.lastname;

It gave me an error of not a single-group group function.

Any idea what is the issue here?

CodePudding user response：

With some sample data

SQL> with
  2  author (authorid, firstname, lastname) as
  3    (select 1, 'Stephen', 'King'  from dual union all
  4     select 2, 'Jo'     , 'Nesbo' from dual),
  5  book (bookid, authorid) as
  6    (select 100, 1 from dual union all
  7     select 200, 1 from dual union all
  8     select 300, 2 from dual
  9    ),
 10  transaction (trans_id, bookid) as
 11    (select 1, 100 from dual union all
 12     select 2, 100 from dual union all
 13     select 3, 100 from dual union all
 14     select 4, 300 from dual
 15    ),

query uses the RANK analytic function which ranks rows by number of rows in the transaction table (it says how many books were sold). Finally, fetch row(s) that rank as highest:

 16  temp as
 17    (select a.firstname || ' ' || a.lastname AS fullname,
 18            count(t.bookid) cnt,
 19            rank() over (order by count(t.bookid) desc) rnk
 20     from author a join book b on a.authorid = b.authorid
 21                   join transaction t on t.bookid = b.bookid
 22     group by a.firstname, a.lastname
 23    )
 24  select fullname, cnt
 25  from temp
 26  where rnk = 1;

FULLNAME             CNT
------------- ----------
Stephen King           3

SQL>

CodePudding user response：

You can use:

select MAX(a.firstname ||' '|| a.lastname) as fullname,
       COUNT(datesold)
from   author a
       INNER JOIN book b
       ON (a.authorid = b.authorid)
       INNER JOIN transaction t
       ON (b.bookid = t.bookid)
GROUP BY
       a.authorid
ORDER BY
       COUNT(datesold) DESC
FETCH FIRST ROW ONLY;

Do not aggregate by firstname and lastname as there are many people in the world with identical names and you do not want to count everyone with the same name as a single person.

Which, for the sample data:

CREATE TABLE author (authorid, firstname, lastname, dateofbirth) AS
SELECT 1, 'Alice', 'Adams', DATE '1900-01-01' FROM DUAL UNION ALL
SELECT 2, 'Alice', 'Adams', DATE '1910-01-01' FROM DUAL UNION ALL
SELECT 3, 'Betty', 'Baron', DATE '1920-01-01' FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Corrs', DATE '1930-01-01' FROM DUAL UNION ALL
SELECT 5, 'Carol', 'Corrs', DATE '1940-01-01' FROM DUAL;

CREATE TABLE book (bookid, authorid) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 5 FROM DUAL;

CREATE TABLE transaction (bookid, datesold) AS
SELECT 1, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 1, DATE '1970-01-02' FROM DUAL UNION ALL
SELECT 1, DATE '1970-01-03' FROM DUAL UNION ALL
SELECT 1, DATE '1970-01-04' FROM DUAL UNION ALL
SELECT 3, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 4, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 4, DATE '1970-01-02' FROM DUAL UNION ALL
SELECT 5, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 5, DATE '1970-01-02' FROM DUAL UNION ALL
SELECT 5, DATE '1970-01-03' FROM DUAL;

Outputs:

FULLNAME COUNT(DATESOLD)

Alice Adams 4

FULLNAME	COUNT(DATESOLD)
Alice Adams	4

db<>fiddle here