CODEWITHSUNDEEP
sqloracle-databaseselectgroup-bysum
user3106078
user3106078

Reputation:

Group by won't work in my statement. WHY

Practice question for final SQL exam. List the total number of collections(fines and fees) and the total amount owned by crime classification.

This is my code. Please tell me why its not working.

select classification, fine_amount+court_fee as "collections amount", fine_amount - amount_paid as "Amount owned"
from crime_charges join crimes using (crime_id)
group by classification;

Error thrown:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 23

Upvotes: 2

Views: 1556

Answers (4)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

@shabeer, I appreciate your interest in your learning. But still, it is always wise to invest your time in theoretical learning. The question of yours is very fundamental. A select query which uses group by can contain only aggregate functions and the columns grouped by in the select clause. Just imagine, how the database know to select a single value of the fine_amount column, when u already grouped the dataa with classification column. It may be either a max, min, sum.. etc.. Not my advise, but I just feel, when you get down to know the basics, you will feel the difference. When you search internet for solutions, it may get as a habbit!

All the best!

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT classification, SUM(fine_amount+court_fee) AS "collections amount", 
       SUM(fine_amount - amount_paid) AS "Amount owned"
FROM crime_charges 
INNER JOIN crimes USING (crime_id)
GROUP BY classification;

Upvotes: 2

Hamidreza
Hamidreza

Reputation: 3128

try this query:

select classification, fine_amount+court_fee as "collections amount", fine_amount - amount_paid as "Amount owned"
from crime_charges join crimes using (crime_id)
group by classification,fine_amount,court_fee,amount_paid;

Upvotes: 0

75inchpianist
75inchpianist

Reputation: 4102

you must put all of your columns in the select clause into the group by clause.

Upvotes: 0

Related Questions