Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.4k points)

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. 

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...