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
Advertisement
Answer
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>