0

To retrieve particular data set I had to use a subquery in a select query which is a group by expression as below:

SELECT SUM(b.RESERVEDROOMS) AS "Reserved Rooms",
       (SELECT country
          FROM location
         WHERE location_id = b.location_id) AS "Country"
  FROM bookingsRevenue b,
       location l
 WHERE b.location_id = l.location_id 
 GROUP BY b.location_id

It works fine and correct data are retrieved but when I try to create a view from it, following error is shown. Could you please help me to resolve this.

[Error] Execution (21: 74): ORA-00979: not a GROUP BY expression

micklesh
  • 417
  • 1
  • 4
  • 16
Manujaya
  • 13
  • 1
  • 5

1 Answers1

2

Seems that there is a bit of confusion in your source code. The GROUP BY clause isn't correct, also you do not need to use a correlated subquery. Try this one:

SELECT SUM(b.RESERVEDROOMS) AS "Reserved Rooms", l.country AS "Country"
FROM bookingsRevenue b
JOIN location l ON (b.location_id = l.location_id)
GROUP BY l.country;

If you want also the location_id to be grouped then:

SELECT SUM(b.RESERVEDROOMS) AS "Reserved Rooms", b.location_id, l.country AS "Country"
FROM bookingsRevenue b
JOIN location l ON (b.location_id = l.location_id)
GROUP BY b.location_id, l.country;
Alessandro Vecchio
  • 186
  • 2
  • 9