CODEWITHSUNDEEP
sqlsql-servert-sql
mig_08
mig_08

Reputation: 181

Sum of a subquery in SQL

I am trying to do a sum of a column with a distinct but seem to be getting this error:

cannot perform an aggregate function on an expression containing an aggregate or a subquery

Query that is currently working:

SELECT A.AdSpendPromotionSpendTypeId , PS.Name, [MajorGroup], [FiscalYear],
    (SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 0) AS Actual,
    (SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 2) AS Committed,
    (SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 1 OR p.PromotionStatusId = 1002) AS Planned
FROM [Promotions] P
JOIN [PromotionAdSpends] A ON P.Id = A.PromotionId
JOIN [opt_PromotionSpendTypes] PS ON PS.Id = A.AdSpendPromotionSpendTypeId
GROUP BY A.AdSpendPromotionSpendTypeId , PS.Name, [MajorGroup],
         [FiscalYear], PromotionStatusId

Output:

Image

Since line 1 and 2 have the same details I want to group them up and add the sum together of the columns.

When I change my query to the below it throws the error

cannot perform an aggregate function on an expression containing an aggregate or a subquery

Not sure what I am missing.

SELECT DISTINCT A.AdSpendPromotionSpendTypeId , PS.Name, [MajorGroup], [FiscalYear],
    SUM((SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 0)) AS Actual,
    SUM((SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 2)) AS Committed,
    SUM((SELECT Sum(Amount)
    WHERE   p.PromotionStatusId = 1 OR p.PromotionStatusId = 1002)) AS Planned
FROM [Promotions] P
JOIN [PromotionAdSpends] A ON P.Id = A.PromotionId
JOIN [opt_PromotionSpendTypes] PS ON PS.Id = A.AdSpendPromotionSpendTypeId
GROUP BY A.AdSpendPromotionSpendTypeId , PS.Name, [MajorGroup],
         [FiscalYear], PromotionStatusId

Upvotes: 1

Views: 110

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Use conditional aggregation:

SELECT A.AdSpendPromotionSpendTypeId, PS.Name, [MajorGroup], [FiscalYear],
       Sum(CASE WHEN p.PromotionStatusId = 0 THEN Amount END) AS Actual,
       Sum(CASE WHEN p.PromotionStatusId = 2 THEN Amount END) AS Committed,
       Sum(CASE WHEN p.PromotionStatusId IN 1, 2) THEN Amount END) AS Planned
FROM [Promotions] P JOIN
     [PromotionAdSpends] A ON P.Id = A.PromotionId JOIN
     [opt_PromotionSpendTypes] PS ON PS.Id = A.AdSpendPromotionSpendTypeId
GROUP BY A.AdSpendPromotionSpendTypeId , PS.Name, [MajorGroup], [FiscalYear]

Upvotes: 3

Related Questions