CODEWITHSUNDEEP
sqloracle-database
user6042889
user6042889

Reputation:

not a single-group group function (00937. 00000)

SELECT p.pdept.dno,
       MAX(SUM(p.budget)) AS max
FROM Proj111 p
GROUP BY p.pdept.dno

Although I have included all the necessary attributes in select statements to group by clause it will generate above issue? How to solve it?

Upvotes: 2

Views: 497

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

You can't nest aggregate functions like that. If you want to get the maximum sum from each group, then you can try this:

WITH cte AS (
    SELECT p.pdept.dno AS dept,
           SUM(p.budget) AS budget
    FROM Proj111 p
    GROUP BY p.pdept.dno
)

SELECT t.dept,
       t.budget
FROM cte t
WHERE t.budget = (SELECT MAX(budget) FROM cte)

The common table expression which I have named cte finds the budgets for each department. The query then restricts this result to the department with the maximum budget by again querying the cte for the maximum budget.

Upvotes: 5

Related Questions