2

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?

  • Provide sample data from these two tables. Also describe what do you want in words. – JSapkota Apr 11 '16 at 2:28
  • Seconded. mysqldump -u user -pdba my_schema my_table > table.sql - post the INSERT INTO My_Table VALUES(....) here. I think it's going to be trick with what you've given us - for example, how do I tell the difference between Bursa-> Izmir and Hatay-> Izmir? We've no origin field in the data you posted! – Vérace Apr 11 '16 at 9:00
  • I use oracle database. I edited tables. – AhmetMelihBaşbuğ Apr 11 '16 at 10:16
  • @AhmetMelihBaşbuğ Have you tried adding bus column on group by clause? Also provide sample data from tickets table. – JSapkota Apr 11 '16 at 10:41
  • You need to group by every non-aggregate column of your query, : GROUP BY by bus, dest – a1ex07 Apr 11 '16 at 10:42
1

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
|improve this answer|||||
-1

I think you have to include the column bus in the group by

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.