CODEWITHSUNDEEP
sqloracle-database
User2013
User2013

Reputation: 409

Oracle throwing "invalid identifier" on COUNT() alias within GROUP BY

I'm trying to return the following three columns: Song ID, Song name, Times Played. This represents a list of requested and played songs.

Assumptions:

  1. Table SONG has SONG_ID and SONG_NAME, one row for each song

  2. Table SONG_REQUEST only has SONG_ID for each time the song is requested

  3. Table PLAY has SONG_ID for each time the song is played

This is my query:

SELECT r.SONG_ID, s.SONG_NAME, COUNT(p.SONG_ID) AS TimesPlayed
FROM PLAY p
INNER JOIN SONG s ON p.SONG_ID = s.SONG_ID
LEFT JOIN SONG_REQUEST r ON p.SONG_ID = r.SONG_ID
GROUP BY p.SONG_ID, s.SONG_NAME, TimesPlayed

The error thrown is:

ORA-00904: "TIMESPLAYED": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 5 Column: 33

Upvotes: 1

Views: 8306

Answers (3)

Álvaro González
Álvaro González

Reputation: 146460

Oracle doesn't allow to use aliases from the SELECT clasuse in the GROUP BY clause (you'd need to write COUNT(p.SONG_ID) again) but it doesn't make much sense to group by TimesPlayed anyway. You probably want to remove it entirely.

Also, you select these columns:

r.SONG_ID, s.SONG_NAME

... but attempt to group by 

p.SONG_ID, s.SONG_NAME

Is it a typo?

Upvotes: 3

Colin 't Hart
Colin 't Hart

Reputation: 7729

Something like this should work:

SELECT r.SONG_ID, s.SONG_NAME, COUNT(p.SONG_ID) AS TimesPlayed
FROM PLAY p
INNER JOIN SONG s ON p.SONG_ID = s.SONG_ID
LEFT JOIN SONG_REQUEST r ON p.SONG_ID = r.SONG_ID
GROUP BY r.SONG_ID, s.SONG_NAME;

Note that in the group by clause I've reference the exact same columns as in the select clause (changed alias p to r).

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

You can't refer to a column alias in the group by or order clauses.

In any case, as Colin points out, you'd actually group by the non-aggregated columns.

Upvotes: 1

Related Questions