SQL ORA-00979 Error Thrown

Question

I'm trying to create a query that returns the name and country of one thing that is in another table the largest number of times.

Here's the structure:

ShipModels(mname, type, country, numGuns, gunSize)
Ships(sname, hasModel, launchYear)
Battles(bname, year)
Outcomes(ship, battle, result)

I've tried many different things, but can't get it to work. This is my current query:

SELECT ship, country
FROM ShipModels, Ships, Outcomes
WHERE hasModel = mname AND ship = sname
GROUP BY ship
HAVING COUNT(ship) >= 
( SELECT COUNT (ship) FROM Outcomes GROUP BY ship);

The Oracle error:

ERROR at line 1:
ORA-00979: not a GROUP BY expression

From what I have read, this error is thrown when there is an aggregate function in the SELECT statement, but...well, there isn't. Is there something I'm missing? How can I get this to work?

If you're going to use HAVING COUNT(ship) I believe you need to include COUNT(ship) in your SELECT — WebChemist, Dec 03 '12 at 00:35

ypercubeᵀᴹ · Accepted Answer · 2012-12-03T01:47:23.853

You include the country in the SELECT:

SELECT ship, country

but not in the GROUP BY. Change that line to:

GROUP BY ship, country

When you correct that, you'll run into another error, because the subquery is not scalar, e.g. does not return a single value:

( SELECT COUNT (ship) FROM Outcomes GROUP BY ship)

You probably meant to write:

( SELECT MAX(cnt)
  FROM
    ( SELECT COUNT(ship) AS cnt FROM Outcomes GROUP BY ship ) t
)

So, the full query will be more or less:

SELECT ship, country
FROM ShipModels, Ships, Outcomes
WHERE hasModel = mname AND ship = sname
GROUP BY ship, country
HAVING COUNT(ship) = 
    ( SELECT MAX(cnt)
      FROM
        ( SELECT COUNT(ship) AS cnt FROM Outcomes GROUP BY ship ) t
    ) ;

You could also write it to be more like your attempt, using the keyword ALL:

SELECT ship, country
FROM ShipModels, Ships, Outcomes
WHERE hasModel = mname AND ship = sname
GROUP BY ship, country
HAVING COUNT(ship) >= ALL
    ( SELECT COUNT(ship) AS cnt FROM Outcomes GROUP BY ship ) ;

It would also be good if you used the "modern" (SQL-92) syntax of explicit joins. Instead of:

FROM ShipModels, Ships, Outcomes
WHERE hasModel = mname AND ship = sname

it's better for various reasons to write:

FROM ShipModels m
  JOIN Ships s ON s.hasModel = m.mname
  JOIN Outcomes o ON o.ship = s.sname

score 0 · Answer 2 · answered Dec 03 '12 at 00:17

0

The issue is with the count(ship) in the HAVING clause. I think that's the aggregate function in the select statement.

answered Dec 03 '12 at 00:17

kdmurray

2,988
3
32
47

SQL ORA-00979 Error Thrown

2 Answers2