Happy Programming

*** Can't nest aggregate function - error

Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, max(Sum(Amount)) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
ORDER BY Y,Q

*** -- Solution: Including Detail description

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category, MaxQuery.MaxSum as sales
FROM (-- Find Max Sum Per Year/Quarter
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
) MaxQuery
JOIN (-- All Amount Query
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
)AllQuery
ON MaxQuery.Y = AllQuery.Y
AND MaxQuery.Q = AllQuery.Q
AND MaxQuery.MaxSum = AllQuery.Sum
ORDER BY MaxQuery.Y, MaxQuery.Q