-2

I'm trying to execute one sql query using joins, but I'm getting the below error:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

The query is to count the number of normalized_event_ids along with the error message and error_ids using two tables.:

select count(nee.normalised_event_id),
       em.error_message,
       em.error_message_id 
from normalised_event_error nee, error_message em 
where nee.charge_start_date >= to_date('01-07-2017','DD-MM-YYYY') 
and nee.error_message_id = em.error_message_id 
group by em.error_message;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
User123
  • 1,498
  • 2
  • 12
  • 26
  • 1
    You didn't name all the SELECT fields into the GROUP BY statement.. you are missing error_message_id in the GROUP BY... – Raymond Nijland Aug 05 '17 at 10:19
  • First of all get rid of these old styled joins. Use the explicit word join and put `em.error_message_id ` in group by clause – Ankit Bajpai Aug 05 '17 at 10:19
  • The fact that your query includes a join doesn't change anything. In Oracle the GROUP BY clause must include all the non-aggregated columns. – APC Aug 05 '17 at 12:39
  • 1
    @AnkitBajpai - some people are obsessed with the use of ANSI 92 syntax. But the older style syntax is **completely irrelevant** to this question. So why lead with that? – APC Aug 05 '17 at 12:41

3 Answers3

3
  SELECT COUNT (nee.normalised_event_id), em.error_message, em.error_message_id
    FROM normalised_event_error nee
         INNER JOIN error_message em
            ON nee.error_message_id = em.error_message_id
   WHERE nee.charge_start_date >= TO_DATE ('01-07-2017', 'DD-MM-YYYY')
GROUP BY em.error_message, em.error_message_id;

The select columns and group by columns must be same for grouping.

mehmet sahin
  • 802
  • 7
  • 21
2

Example

You need to add error_message_id column to the group by clause:

SELECT COUNT(nee.normalised_event_id) as messages,
       em.error_message,
       em.error_message_id 
FROM normalised_event_error nee
    JOIN error_message em ON em.error_message_id = nee.error_message_id
WHERE nee.charge_start_date >= to_date('01-07-2017','DD-MM-YYYY') 
GROUP BY em.error_message, em.error_message_id;
vppuzakov
  • 81
  • 5
1

Try this:

SELECT
    em.error_message,
    em.error_message_id, 
    count(nee.normalised_event_id) 
from normalised_event_error nee, 
     error_message em 
where nee.charge_start_date >= to_date('01-07-2017','DD-MM-YYYY') 
and nee.error_message_id = em.error_message_id 
group by em.error_message, em.error_message_id;
APC
  • 144,005
  • 19
  • 170
  • 281
kbn1
  • 26
  • 3