Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article.
ORA-00979: not a group by expression
The ORA-00979 error happens when you have at least one column in your SELECT clause that is not in your GROUP BY expression when you are using an aggregate function.
Common aggregate functions include SUM, AVG, MIN, MAX, and COUNT. Any column or expression in your SELECT clause must also be listed in the GROUP BY clause.
Here’s an example of a query that will generate the error:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;
Result:
ORA-00979: not a GROUP BY expression
Why Do I Get The ORA-00979 Error?
This error happens because you’re using an aggregate function, and there is at least one column in the SELECT clause that is not in the GROUP BY clause.
Using the example query above:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name;
Because I use an aggregate function (COUNT), I need to define all of the columns in a GROUP BY clause that are in the SELECT clause.
In this example, I have specified the first_name and last_name columns in the SELECT clause, but the last_name column is not in the GROUP BY clause.
Even if I have some fields in the GROUP BY clause, if I don’t specify all of the fields from the SELECT clause, I’ll still get an error.
For example:
SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY first_name, last_nameResult:
ORA-00979: not a GROUP BY expression
As you can see, this will still give me an error.
Why does Oracle give an error?
Because, if you don’t have a GROUP BY but you want to SELECT the column, Oracle doesn’t know what value to show when using this aggregate function. Should it show the first value? The last value? A random value?
How To Resolve the ORA-00979 Error
To resolve the ORA-00979: not a group by expression error, simply ensure that all of the GROUP BY columns match the SELECT clause.
You can do this by adding columns to the GROUP BY.
So, using the example above:
SELECT first_name, last_name, COUNT(*)
FROM student
GROUP BY first_name, last_name;Result:
| FIRST_NAME | LAST_NAME | COUNT(*) |
| John | Smith | 2 |
| Mark | Anderson | 1 |
| Michael | Condor | 1 |
| Brendan | Jefferson | 1 |
| Peter | Stark | 1 |
| Sally | Lincoln | 1 |
| Michelle | Brumby | 1 |
| Amy | Ford | 1 |
| Rose | Minson | 1 |
| Tina | Mitchell | 1 |
Or, using the second example:
SELECT first_name, last_name, address_state, COUNT(*)
FROM student
GROUP BY address_state, first_name, last_nameResult:
| FIRST_NAME | LAST_NAME | ADDRESS_STATE | COUNT(*) |
| John | Smith | California | 1 |
| Mark | Anderson | Texas | 1 |
| Michael | Condor | Florida | 1 |
| Brendan | Jefferson | Florida | 1 |
| Peter | Stark | Illinois | 1 |
| Sally | Lincoln | California | 1 |
| Michelle | Brumby | Texas | 1 |
| Amy | Ford | Texas | 1 |
| Rose | Minson | California | 1 |
| Tina | Mitchell | Illinois | 1 |
| John | Smith | Texas | 1 |
The columns don’t need to be in the same order to correct the error. They just need to be present.
One thing to remember with a GROUP BY clause, is if you give your columns aliases, you need to specify the original column or expression.
So, if you have a query like this:
SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY full_name;Result:
ORA-00904: "FULL_NAME": invalid identifier
You get an error because you can’t refer to a column alias within the GROUP BY clause. You’ll need to use a query like this:
SELECT first_name + ' ' + last_name AS full_name, COUNT(*)
FROM student
GROUP BY first_name + ' ' last_name;| FULL_NAME | COUNT(*) |
| John Smith | 2 |
| Mark Anderson | 1 |
| Michael Condor | 1 |
| Brendan Jefferson | 1 |
| Peter Stark | 1 |
| Sally Lincoln | 1 |
| Michelle Brumby | 1 |
| Amy Ford | 1 |
| Rose Minson | 1 |
| Tina Mitchell | 1 |
Conclusion
So, in conclusion, the ORA-00979: not a group by expression error happens because the columns in the SELECT clause don’t match the columns in the GROUP BY clause. To resolve the error, make sure the columns match.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
How can I use a SUM command to get subtotal for project_price with this query I have written.
select
a.activity_description,
a.resource_id as resource_id,
b.resource_group_desc,
a.qty_used as qty_used,
a.qty_planned as qty_planned,
a.project_cost,
a.percent_used
from IFSAPP.activity_resource_proj a JOIN IFSAPP.activity_resource_proj_sum b
ON a.resource_id = b.resource_id
AND a.project_id = ‘&Project_ID’
ORDER BY activity_description
Hi Olusegun, you can use a query like this one. You can add the SUM function with project_price, and GROUP BY all of the other fields. I assume your project price is in table “a”:
SELECT
a.activity_description,
a.resource_id as resource_id,
b.resource_group_desc,
a.qty_used as qty_used,
a.qty_planned as qty_planned,
a.project_cost,
a.percent_used,
SUM(a.project_price) AS project_price_total
FROM IFSAPP.activity_resource_proj a JOIN IFSAPP.activity_resource_proj_sum b
ON a.resource_id = b.resource_id
AND a.project_id = ‘&Project_ID’
GROUP BY
a.activity_description,
a.resource_id
b.resource_group_desc,
a.qty_used
a.qty_planned
a.project_cost,
a.percent_used
ORDER BY activity_description;
could you please help me how to apply the group by function with the below query, i want to findthe max value of sequence
select a.request_no, a.order_state,a.summary,a.impact,a.priority,a.reported_date RIR_crt_date,a.reported_user SIR_NO,a.contact_person_city, a.contact_person_postcode,a.contact_person_house_number,a.xdf_service_instance_id,a.service_id,a.wso_id,a.carrier_type,
b.field_value ANALYSIS_CODE , max(b.seq)
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’ANALYSIS_CODE_DESCRIPTION’) ANALYSIS_CODE_DESCRIPTION
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’CLIENT_TICKET_ID’) CLIENT_TICKET_ID
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’BOL_CODE’) BOL_CODE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’BOL_CODE_DESCRIPTION’) BOL_CODE_DESCRIPTION
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’CARRIER_TECHNOLOGY_TYPE’) CARRIER_TECHNOLOGY_TYPE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’CFS_NAME’) CFS_NAME
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’INTERFACE_PROBABLE_CAUSE_CODE’) PROBABLE_CAUSE_CODE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’SYMPTOM_CODE’) SYMPTOM_CODE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’SYMPTOM_CODE_DESCRIPTION’) SYM_CODE_DESC
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’INTERFACE_PROBABLE_CAUSE_CODE_DESCRIPTION’) PROBABLE_DESC
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’PRODUCT_TYPE’) PRODUCT_TYPE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’RESOLUTION_GROUP’) RESOLUTION_GROUP
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’RESOLVING_PARTY_SYSTEM’) RESOLVING_PARTY_SYSTEM
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’SOURCE’) SOURCE
,(select max(field_value) from OF_TRACE_DETAILS where a.request_no=request_no and field_code=’SYMPTOM_DESCRIPTION’) SYMPTOM_DESC
,(select workorder_no from of_work_order where a.request_no=request_no and workorder_type=’LOTTE’) LOTTE_TASK
,(select workorder_no from of_work_order where a.request_no=request_no and workorder_type=’VWT’) VWT_TASK
,(select workorder_no from of_work_order where a.request_no=request_no and workorder_type=’REGGEFIBER’) REGGEFIBER
from OF_REQUEST a ,OF_TRACE_DETAILS b
where a.state=’A’
and a.request_no=b.request_no
and b.field_code=’ANALYSIS_DESCRIPTION’
and b.state=’A’
and a.request_no=’RIR202104126058′
group by b.field_value;
Hi
Maybe this is an easy one for you to answer, I think the statemant is simple but not for me…
The original statement uses a MAX for finding correct row but now there is a problem and I need the MIN as the aggregator to find the correct row.
But then the problem with CASE together with GROUP BY and I get the ORA-00979: not a group by expression
I have searched and tried to follow other people with the same error and the suggested solutions but I dont get it to work due to a lot of trail and error.
The statement is a small portion of the SQL
This statement works and get me the MAX value
SELECT
TRANSMAIN.TRANSEX AS TRANSEX,
MAX(TRANSLOG.TRANSIK)AS TRANSIK
FROM
tmsdat.TRANSLOG TRANSLOG,
tmsdat.TRANSMAIN TRANSMAIN,
tmsdat.TRCBUS TRCBUS
WHERE
TRANSMAIN.TRCBUSNO=TRCBUS.TRCBUSNO
AND TRANSMAIN.TRANSIK >=5246867
AND TRANSMAIN.TRANSIK=TRANSLOG.TRANSIK
GROUP
BY TRANSMAIN.TRANSEX
But this modified statement gets the error
SELECT
TRANSMAIN.TRANSEX AS TRANSEX,
(case when TRANSMAIN.TRCBUSNO = 1282
MIN(TRANSLOG.TRANSIK) else
MAX(TRANSLOG.TRANSIK) end ) AS TRANSIK
FROM
tmsdat.TRANSLOG TRANSLOG,
tmsdat.TRANSMAIN TRANSMAIN,
tmsdat.TRCBUS TRCBUS
WHERE
TRANSMAIN.TRCBUSNO=TRCBUS.TRCBUSNO
AND TRANSMAIN.TRANSIK >=5246867
AND TRANSMAIN.TRANSIK=TRANSLOG.TRANSIK
GROUP
BY TRANSMAIN.TRANSEX
Hope you can help
Regards /Stefan
Pouvez vous m’aider svp, avec cette requete , j’ai cette erreur :etch first ? rows only, Error Msg = ORA-00979: n’est pas une expression GROUP BY, je ne trouve pas de solution
strQuery = “select a.codeCommercial from Affectation a INNER JOIN Client c on c.id = a.client.id ”
+” where sysdate between a.dateDebut and a.dateFin ”
+” and a.codeClient = c.code ”
+” and c.etat.code = ‘OUV’ ”
+” and c.apporteur.code != 52417 ”
+” and SUBSTRING(c.codePostalRecherche,0,1) = SUBSTRING(CODE_POSTAL_RECHERCHE,0,1) ”
+” group by a.codeCommercial”
+” having count(*) = (select max(COUNT(c.code)) from Affectation a, Client c ”
+” where sysdate between a.dateDebut and a.dateFin and c.etat.code = ‘OUV’ and a.codeClient = c.code and c.apporteur.code != 52417 and SUBSTRING(c.codePostalRecherche,0,1) = SUBSTRING(CODE_POSTAL_RECHERCHE,0,1) group by a.codeCommercial) “;