CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
user1819786
user1819786

Reputation: 133

SQL error ORA-00979 Group By error

So the question asks me that: "What is the average cost of service visits on 2009 Mercedes?" I am therefore joining two tables: ServInv and Car, to get the result but keep getting an error:

SELECT car.make, car.model, servinv.totalcost, AVG(servinv.totalcost)
FROM s2.servinv 
INNER JOIN s2.car 
ON servinv.cname = car.cname 
WHERE car.make = 'MERCEDES'
AND car.cyear = '2009'
GROUP BY servinv.totalcost
;

What am I doing wrong here?

select * from s2.car;

This gives the following: https://i.sstatic.net/P8svQ.png

And:

select * from s2.servinv;

Gives the following: https://i.sstatic.net/mYmm5.png

As for the description of the tables, the Car table looks like this: https://i.sstatic.net/Py80K.png

And the Servinv table is as follows: https://i.sstatic.net/fjPuL.png

Upvotes: 0

Views: 80

Answers (1)

zedfoxus
zedfoxus

Reputation: 37059

Have your dimensions such as make and model in group by. Include statistics with your numeric fields.

SELECT car.make, car.model, SUM(servinv.totalcost), AVG(servinv.totalcost)
FROM s2.servinv 
INNER JOIN s2.car 
ON servinv.cname = car.cname 
WHERE car.make = 'MERCEDES'
AND car.cyear = '2009'
GROUP BY car.make, car.model;

This way, we tell the DB to give us totalcost (total of totalcost and average of the total) from all records of 2009 Mercedes.

Upvotes: 1

Related Questions