ORA-00979 not a group by expression

Question

I am getting ORA-00979 with the following query:

SELECT cr.review_sk, cr.cs_sk, cr.full_name,

tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",

cs.cs_id, cr.tracking_number

from review cr, cs, fact cf

where cr.cs_sk = cs.cs_sk

and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%'

and row_delete_date_time is null

and cr.review_sk = cf.review_wk (+)

and cr.fact_type_code (+) = 183050

GROUP BY cr.review_sk, cr.cs_sk, cf.fact_date, cr.tracking_number

ORDER BY cs.cs_id, cr.full_name;

I couldn't find any examples that had both GROUP BY and ORDER BY clauses in the same query. I tried removing each field from the group by one at a time, but am still getting the same error.

Answer 1

You should just put all columns of the SELECT in the GROUP BY or you can use the functions on them which compress the results to a single value like SUM or MIN, MAX.

An example is as follows: Assume, you have a database like this:

FOO      BAR

0         A

0         B

and if you are trying to run 

SELECT * FROM table GROUP BY foo 

It means that the database must return a single row as a result with the first column 0 to fulfill the GROUP BY but there are now two values of the bar to choose from.