Stack Overflow
Stack Overflow

Questions tagged [ora-00979]

ORA-00979: not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

51 questions
213
votes
6 answers

GROUP BY with MAX(DATE)

I'm trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK 13:00 2 SH …
Aries
  • 2,191
  • 2
  • 15
  • 7
188
votes
10 answers

ORA-00979 not a group by expression

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)…
Theresa
  • 3,515
  • 10
  • 42
  • 47
10
votes
1 answer

SQL GROUP BY - Using COUNT() function

I've been doing a task which involves creating a database for a hospital and I've been encountering a very frustrating error which I cannot seem to fix no matter how much research that I do. The error I received is: ERROR at line 1: ORA-00979: not…
Nifty
  • 117
  • 1
  • 1
  • 9
7
votes
4 answers

SQL Group By function(column) - Now can't Select that column

I used the HR employee schema in Oracle Express and I wanted to select employees that were hired on a particular year. SELECT hire_date, COUNT(*) FROM employees empl GROUP BY SUBSTR(hire_date, -4) ORDER BY empl.hire_date; The…
Mark Estrada
  • 9,013
  • 37
  • 119
  • 186
7
votes
4 answers

Query works with Oracle 10g but not with 11g?

DECLARE trn VARCHAR2(2) := 'DD'; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT TRUNC(some_date, trn), NULL AS dummy_2, COUNT( DISTINCT dummy_1 ) FROM (SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual) …
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
6
votes
3 answers

ORA-00979: not a GROUP BY expression with a simple example

I tried the following example in Oracle 11g: http://joshualande.com/filters-joins-aggregations/ SELECT c.recipe_name, COUNT(a.ingredient_id), SUM(a.amount*b.ingredient_price) FROM recipe_ingredients a JOIN ingredients b ON a.ingredient_id =…
dsz36
  • 127
  • 1
  • 7
5
votes
1 answer

Grails groupProperty and order. How it works?

I have this domain : class Participation { ParticipationStatus status } class ParticipationStatus{ String name Date creationDate } I create a query : Participation.createCriteria().list{ createAlias("status","statusAlias") order…
Jonathan Lebrun
  • 1,462
  • 3
  • 20
  • 42
4
votes
5 answers

'group by' works on MySQL, but not Oracle

I have a query that works on MySQL but doesn't work on Oracle, and I'm trying to convert. This is my table: unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 0 …
Kevin
  • 11,521
  • 22
  • 81
  • 103
3
votes
2 answers

Strange periodic group by problems (ORA-00979)

We are using the following SQL query to produce monthly averages. The statement is heavy used and works pretty well, but it fails with a 'ORA-00979: not a GROUP BY expression' every month or two and we have no idea why. First about the process: we…
reto
  • 16,189
  • 7
  • 53
  • 67
3
votes
1 answer

Queries on Views cause strange errors in Oracle SQL Developer

I have to write an SQL command which queries a View in a database. I did not create them myself nor do I have access to the tables. Only the views are provided to us by our software manufacturer. I'm using Oracle SQL Developer 3.0 to query. The…
user748261
  • 151
  • 1
  • 2
  • 6
2
votes
3 answers

SQL help with MAX query

I have a table of countries named bbc(name, region, area, population, gdp) I want a table with the region, name and population of the largest ( most populated) countries by region. So far i've tried this: SELECT region, name, MAX(population) FROM…
tiagovrtr
  • 162
  • 3
  • 15
2
votes
2 answers

ORA-00979: not a GROUP BY expression issue

I am performing the following request and I get a "ORA-00979: not a GROUP BY expression" error. select distinct field1, field2, field3, count(*) as field4, field5, field6, case when smt1>0 then 'Deleted' when smt2>0 then 'Impacted' when smt3>0…
MartinMoizard
  • 6,600
  • 12
  • 45
  • 75
2
votes
4 answers

Oracle ORA-00979 - "not a GROUP BY expression"

Can anybody please help me with this particular query? I'm having ORA-00979 when trying to run this: select t0.title, count (1) as count0, (select count (1) from contract c1, se se1 where c1.c_id = se1.c_id and se1.svc_id = 3 …
Arino
  • 95
  • 3
  • 9
2
votes
2 answers

Encountering ORA-00979: not a GROUP BY expression when using CASE - IN statements in sql

This works: SELECT (CASE WHEN x = 'value' THEN a.col1 ELSE nvl(a.col1, a.col2) END) FROM table1 a WHERE a.this = 'that' GROUP BY (CASE WHEN x = 'value'…
jonasespelita
  • 1,660
  • 5
  • 25
  • 31
2
votes
3 answers

ORA-00979: not a GROUP BY expression Error, how to resolve?

SELECT ssn, fname, minit, lname, AVG(hours) FROM EMPLOYEE, WORKS_ON WHERE EMPLOYEE.ssn = WORKS_ON.essn GROUP BY hours ORDER BY AVG(hours) DESC It shows error of ORA-00979: not a GROUP BY expression I wonder why its not working? Btw, all table…
weia design
  • 1,250
  • 2
  • 14
  • 22
1
2 3 4