问题描述:

This question already has an answer here:

  • ORA-00979 not a group by expression

    5 answers

网友答案:

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  
网友答案:

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

相关阅读:
Top