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)
开发者_C百科 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;
In your first inner SELECT, you should either remove "zaloha", GROUP BY it or apply some aggregate function to it.
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
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
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
精彩评论