sqloracle-databaseora-00979

Oracle SELECT help


Here is my SELECT query:

SELECT
    a.id_auto,
    SUM(pozicane_dni * a.poplatok_denny + najazdene_km * a.poplatok_km) celkova_trzba
FROM Auta a
INNER JOIN (SELECT
            id_auto,
            (SUM(koniec_pozicania - zaciatok_pozicania)) pozicane_dni, 
            (SUM(najazdene_km)) najazdene_km,
            zaloha 
            FROM Zakaznik GROUP BY id_auto) z
ON z.id_auto = a.id_auto 
INNER JOIN (SELECT
            id_auto,
            poplatok_denny,
            poplatok_km 
            FROM Auta_zaloha) az
ON az.id_auto = a.id_auto 
GROUP BY a.id_auto;

But I'm getting this error:

ORA-00979: not a GROUP BY expression

Anybody knows where could be the problem? I'm a little bit confused. I have GROUP BY clause everywhere where I use aggregate function SUM().

EDIT:

One more thing, it stop working when I add a CASE WHEN to the query:

SELECT
    a.id_auto, a.poplatok_denny, a.poplatok_km,  
    CASE WHEN z.zaloha IS NULL THEN
        (pozicane_dni * a.poplatok_denny + najazdene_km * a.poplatok_km)
    ELSE 
        (pozicane_dni * az.poplatok_denny + najazdene_km * az.poplatok_km)
    END
        celkova_trzba
FROM Auta a
INNER JOIN (SELECT
            id_auto,
            (SUM(koniec_pozicania - zaciatok_pozicania)) pozicane_dni, 
            (SUM(najazdene_km)) najazdene_km,
            zaloha 
            FROM Zakaznik GROUP BY id_auto, zaloha) z
ON z.id_auto = a.id_auto 
INNER JOIN (SELECT
            id_auto,
            poplatok_denny,
            poplatok_km 
            FROM Auta_zaloha) az 
ON az.id_auto = a.id_auto 
GROUP BY a.id_auto;

Solution

  • In your first inner SELECT, you should either remove "zaloha", GROUP BY it or apply some aggregate function to it.