Unable to MAX(COUNT) and have multiple columns

Question

I have 4 tables, EMPLOYEE, DRIVER, TRIP and TRIPLEG

EMPLOYEE table has Name which I want to extract and show with MAX count, has E# which is shared with DRIVER DRIVER table has L#(licence number) which is common with TRIP

TRIP table has T#(trip number) which is common with TRIPLEG

I'm trying to find the max number of tripleg a driver has done(In this case driver with licence number 10002:

SELECT MAX(COUNT(TRIPLEG.LEG#))
FROM TRIP, TRIPLEG
ON TRIP.T# = TRIPLEG.T#
WHERE TRIP.L# = 10002
GROUP BY TRIP.T#

COUNT(TRIPLEG.LEG#) gives me https://i.imgur.com/AYAovov.png,

so I did the above MAX(COUNT(TRIPLEG.LEG#)) which gives me this: https://i.imgur.com/alCFlO3.png

I'm unable to proceed as I tried SELECTING more columns(TRIP.T#) like

SELECT TRIP.T#, MAX(COUNT(TRIPLEG.LEG#))
FROM TRIP, TRIPLEG
ON TRIP.T# = TRIPLEG.T#
WHERE TRIP.L# = 10002
GROUP BY TRIP.T#

Gives me an error: ORA-00937: not a single-group group function

Any advice? Need to be able to start small and selecting before I can join more tables to get the Employee name displayed beside the MAX tripleg count

Thanks in advance

Essentially I want something like: (only 1 row, which is the MAX triplegs done (5))

NAME     MAX(COUNT(TRIPLEG.LEG#))
-----------------------------------
BOB      5

Answer 1

I don't have your table so I'll use Scott's EMP and DEPT (as you use Oracle, so I presume you're familiar with them).

This works:

SQL> select d.dname, count(e.empno) cnt
  2  from emp e join dept d on e.deptno = d.deptno
  3  where d.deptno in (10, 20)
  4  group by d.dname;

DNAME                 CNT
-------------- ----------
ACCOUNTING              3
RESEARCH                5      --> MAX count is this

SQL>

Nested count works if there are no other columns in SELECT column list (you already know that), and it returns desired value:

SQL> select max(count(e.empno)) cnt
  2  from emp e join dept d on e.deptno = d.deptno
  3  where d.deptno in (10, 20)
  4  group by d.dname;

       CNT
----------
         5

SQL>

But, this won't work (you know that too):

select d.dname, max(count(e.empno)) cnt
from emp e join dept d on e.deptno = d.deptno
where d.deptno in (10, 20)
group by d.dname;

To fix it, use CTE (Common Table Expression a.k.a. the WITH factoring clause) or an inline view; I'll show you the first option, with yet another addition: I'll rank counts and find the "highest" one, and use it later to select only desired row.

SQL> with tcnt as
  2    (select d.deptno,
  3            d.dname,
  4            count(e.empno) cnt,
  5            rank() over (order by count(e.empno) desc) rnk  --> rank them DESC
  6     from emp e join dept d on e.deptno = d.deptno
  7     where d.deptno in (10, 20)
  8     group by d.dname, d.deptno
  9    )
 10  select t.deptno, t.dname, t.cnt
 11  from tcnt t
 12  where rnk = 1;           --> fetch only row(s) with highest rank

    DEPTNO DNAME                 CNT
---------- -------------- ----------
        20 RESEARCH                5

SQL>

Finally, add some more columns from other tables:

SQL> with tcnt as
  2    (select d.deptno,
  3            d.dname,
  4            count(e.empno) cnt,
  5            rank() over (order by count(e.empno) desc) rnk
  6     from emp e join dept d on e.deptno = d.deptno
  7     where d.deptno in (10, 20)
  8     group by d.dname, d.deptno
  9    )
 10  select t.deptno, t.dname, t.cnt, e.ename, e.job
 11  from tcnt t join emp e on e.deptno = t.deptno
 12  where rnk = 1;

    DEPTNO DNAME                 CNT ENAME      JOB
---------- -------------- ---------- ---------- ---------
        20 RESEARCH                5 SMITH      CLERK
        20 RESEARCH                5 JONES      MANAGER
        20 RESEARCH                5 SCOTT      ANALYST
        20 RESEARCH                5 ADAMS      CLERK
        20 RESEARCH                5 FORD       ANALYST

SQL>