I try to fix an SQL i did Earlier and have a problem while doing that.
I have an Huge SQL witch Calculate SUM Fields for a Report.
ATM The SQL Counts Every Vehicle, even when its a Electro Car.
CASE
WHEN AVG(mov.TOTALCONSUMPTION) >0
AND AVG((moving.TOTALTIME)) >0
THEN ROUND(AVG(mov.TOTALCONSUMPTION) / (AVG(mov.TOTALTIME)/3600), 4)
ELSE 0
END ConsumptionPerHour,
I need the Condition Where vehicle.energysource = 0
in my sql,
I try to build it in but allways get the err. "not a GROUP BY expression"
CASE
WHEN AVG(mov.TOTALCONSUMPTION) >0
AND AVG((moving.TOTALTIME)) >0
AND vehicle.ENERGYSOURCE= 0
THEN ROUND(AVG(mov.TOTALCONSUMPTION) / (AVG(mov.TOTALTIME)/3600), 4)
ELSE 0
END ConsumptionPerHour,
I know what the Error Mean but i have no Idear how to fix this.
Anyone have a Idear how i can solve this Problem?
(Btw its Oracle sql developer if that is helpfull)
Edit:
My Exact problem is that i cant put it in the Where part becouse my SQL have to Calculate some things with Electro Cars too like The driven Killometers Ect.
I have the Fuel Problem becouse i want to calculate the avg.
Lets say Car 1 is a Electro Car and Car to a Diesel Car.
The 1 one have a Consumption of 0(becouse Electric car) and the 2. one of 20.
My SQL now Calculate Sum of Consumption (Car 1+Car2 = 20) / amount of Cars:
Thats make a Avg Consumption of 10.
But i only need the Avg Consumption of all cars who are not electric cars.
Maybe a noob Question but im a Trainee and alone in the Office, so sorry if thats an Easy or bad explained question.