## Facts

*** 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