Oracle Min - ORA-00937 不是單組組函式

我試圖找到其票價具有目的地最低票價且不止一家公司使用該目的地的公司。我的命令；

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

我想要的輸出：

巴士票價目的地
---------- ---------- -------------------------
10 32.83 布爾薩
2 30.00 伊茲密爾

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

當我嘗試分組dest時，它給出了錯誤。我不明白什麼是錯誤？有沒有人想解決它？

— 編輯表格 —

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.

• 編輯 -

我使用了此命令，但僅顯示 1 個城市

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

也應該有其他目的地。問題是；

為每個城市找到最便宜的公司，以便超過 1 家公司前往該城市？

你想聚合一個聚合，所以最簡單的方法是分組兩次，我相信。您可能還想重命名我在這裡使用的子查詢表別名，以使它們更有意義。

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

引用自：https://dba.stackexchange.com/questions/134911