Oracle SELECT help

Question

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;

Answer 1

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

Answer 2

The problem will be in using the value of

a.poplatok_km

If you're grouping by a.id_auto, you need to give a rule on what to do if there are multiple rows in the result set (even if there is only one possible row, that is something the SQL doesn't "know").

Two ways around this:

• add all columns from table a that are required in the calculation in the group by clause
• use a "pseudo-Function" like min (a.poplatok_km) which doesn't change anything

Answer 3

Your second inner select:

INNER JOIN (SELECT
        id_auto,
        poplatok_denny,
        poplatok_km 
        FROM Auta_zaloha) az

Appears to be missing a group by clause like:

INNER JOIN (SELECT
        id_auto,
        poplatok_denny,
        poplatok_km 
        FROM Auta_zaloha group by id_auto) az

Answer 4

Try using NVL2 function in place of CASE WHEN, at least it could yield more understandable error message:

    NVL2(z.zaloha,
(pozicane_dni * az.poplatok_denny + najazdene_km * az.poplatok_km),
(pozicane_dni * a.poplatok_denny + najazdene_km * a.poplatok_km)
) celkova_trzba