Oracle – ORA-00937 Not a Single-Group Group Function Error

group byoracleoracle-11g-r2

I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command;

SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id;

       BUS       FARE DEST
---------- ---------- -------------------------
        11      21.36 Kayseri
         1      23.32 Kayseri
         1      63.32 Kars
         5      44.31 Edirne
         1      54.13 Edirne
         1      33.36 Fatsa
         4      33.37 Ankara
         4      33.39 Kayseri
         7      53.37 Istanbul
        10      51.36 Izmir
         7      51.36 Izmir
        10      32.35 Ankara
        10      42.34 Istanbul
        10      52.33 Hatay
        10      32.83 Bursa
         2      30.00 Izmir
        11      52.00 Kayseri
        11      42.00 Kayseri
         2      40.00 Kayseri

Output I want:

       BUS       FARE  DEST
---------- ---------- -------------------------
        10      32.83 Bursa
         2      30.00 Izmir 

select bus, min(fare), dest from 
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;

>ERROR at line 1: ORA-00937: not a single-group group function

When I try to group dest, It gives error. I can't understand what is mistake? Has anyone idea to fix it?

— EDIT TABLES —

CREATE TABLE buscompanies (
      bus_id  number(4) not null PRIMARY KEY,
      name    varchar2(25) not null);

table created.

CREATE TABLE route (
     route_id number(4) not null PRIMARY KEY,
     origin   varchar2(25) not null,
     destination varchar2(25) not null);

table created.

CREATE TABLE tickets (
     tickets_id number(4) not null PRIMARY KEY,
     route_id number(4) not null,
     bus_id number(4) not null,
     fare   number(5,2) not null,
     CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
     CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));


SQL> select * from route;

  ROUTE_ID ORIGIN                    DESTINATION
---------- ------------------------- -------------------------
         1 Ankara                    Kayseri
         2 Ankara                    Kars
         3 Ankara                    Edirne
         4 Ankara                    Fatsa
         5 Denizli                   Ankara
         6 Denizli                   Kayseri
         7 Hatay                     Istanbul
         8 Hatay                     Izmir
         9 Izmir                     Ankara
        10 Izmir                     Istanbul
        11 Izmir                     Hatay
        12 Izmir                     Bursa
        13 Bursa                     Izmir
        14 Bursa                     Kayseri

14 rows selected.

–EDIT–

I used this command but that shows only for 1 city

select bus, min(fare), dest from 
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest 
     from tickets left join route on route.route_id=tickets.route_id) 
     group by bus, dest having count(dest) > 1;

       BUS  MIN(FARE) DEST
---------- ---------- -------------------------
        11      21.36 Kayseri

there should be other destinations too. Question was;

find cheapest company for each city such that more than 1 company
travels to the city?

Best Answer

You want to aggregate an aggregate, so the easiest way to see this is to group twice, I believe. You may also want to rename the subquery table aliases I've used here to make them more meaningful.

SELECT sub2_.bus, MIN(sub2_.fare), MIN(sub2_.dest)
  FROM (SELECT sub1_.bus, MIN(sub1_.fare) AS fare, sub1_.dest
          FROM (SELECT tickets.bus_id    AS bus,
                       tickets.fare      AS fare,
                       route.destination AS dest
                  FROM tickets
                  LEFT JOIN route
                    ON route.route_id = tickets.route_id) sub1_
         GROUP BY sub1_.bus, sub1_.dest
        HAVING COUNT(sub1_.bus) > 1) sub2_
 GROUP BY sub2_.bus
HAVING COUNT(*) > 1
Related Question