Oracle Group By expression not valid

Question

I tried to retrieve some results from a query in my Oracle tables.

The objective is to retrieve the total amount of prize money won by all competitors that belong to 3 countries:

This is my current query:

SELECT NATIONALITY, TOTALPRIZEMONEY
FROM COMPETITOR
WHERE NATIONALITY ='USA'
AND NATIONALITY = 'AUSTRALIA'
AND NATIONALITY = 'SINGAPORE'
GROUP BY NATIONALITY;

Below is a screenshot of my table structure:

I am getting the error:

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

May I request help in this question? Thank you

To SELECT a field it must either be in your GROUP BY or be an aggregate. SELECT NATIONALITY, SUM(TOTALPRIZEMONEY) or GROUP BY NATIONALITY, TOTALPRIZEMONEY. — MatBailie
– MatBailie, Commented Nov 26, 2015 at 16:25
@MatBailie You are totally right and rather than the provided answer your comment is the bets explanation. You should add it as an answer. — Jorge Campos
– Jorge Campos, Commented Nov 26, 2015 at 16:28

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

GROUP BY is used with aggregate queries. For example...

SELECT NATIONALITY, sum(TOTALPRIZEMONEY)
FROM COMPETITOR
WHERE NATIONALITY IN ('USA', 'AUSTRALIA', 'SINGAPORE')
GROUP BY NATIONALITY;

In your query, you aren't aggregating anything, therefore, there is no reason to GROUP BY.

From Oracle documentation on GROUP BY

A GROUP BY clause, part of a SelectExpression, groups a result into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.

You typically use a GROUP BY clause in conjunction with an aggregate expression.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 26, 2015 at 16:26

Drumbeg

1,9441 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jorge Campos Over a year ago

That's not entiry true. You can use group by without an aggregate function, you just need to add all fields of the select on the group by clause!

gymcode Over a year ago

@Drumbeg thank you for the help. However, I am receiving empty colums in the results. The query is able to run but results are not being shown.

MatBailie Over a year ago

@gymcode - That's because you have Nationality = 'x' AND Nationality = 'y'. But no one row can ever be both, and so no one row ever passes your WHERE clause's criteria. I expect you mean Nationality = 'x' OR Nationality = 'y' or perhaps you could use Nationality IN ('x', 'y', 'z')

Mihai Over a year ago

WHERE NATIONALITY IN ('USA','AUSTRALIA','SINGAPORE')

Jorge Campos Over a year ago

@gymcode check out this answer to a detailed explanation on your problem: stackoverflow.com/a/33834473/460557

|

Gordon Linoff · Accepted Answer · 2015-11-26 16:31:38Z

1

The correct query looks more like this:

SELECT SUM(TOTALPRIZEMONEY)
FROM COMPETITOR
WHERE NATIONALITY IN ('USA', 'AUSTRALIA', 'SINGAPORE');

You only need the GROUP BY if you want the results broken out by nationality.

answered Nov 26, 2015 at 16:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle Group By expression not valid

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related