Skip to content
Advertisement

Unable to MAX(COUNT) and have multiple columns

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:

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

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))

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:

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

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

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.

Finally, add some more columns from other tables:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement