SQL-Case / Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Question

Here's my code, which I'm trying to get working:

DECLARE @DATE AS SMALLDATETIME ,
    @BasTar DATE ,
    @Bittar DATE 

SET @DATE = GETDATE() - 1
SET @BasTar = GETDATE() - 1
SET @Bittar = GETDATE() - 1


SELECT  ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END), 0) AS SATIST ,
        ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END), 0) AS SATISIV ,
        ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END), 0) AS IADET ,
        ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END), 0) AS IADEIV ,
        ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END), 0) AS SATISMIK ,
        ISNULL(SUM(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END), 0) AS IADEMIK
FROM    TRANSACTION_HEADER TH WITH ( NOLOCK )
        INNER JOIN TRANSACTION_SALE TS WITH ( NOLOCK ) ON TS.FK_TRANSACTION_HEADER = TH.ID
        INNER JOIN STORE ST WITH ( NOLOCK ) ON ST.ID = TH.FK_STORE
                                               AND ST.NUM BETWEEN '3999' AND '9999'
WHERE   TH.STATUS = 0
        AND ST.DESCRIPTION NOT LIKE 'POL_%'
        AND ST.DESCRIPTION NOT LIKE 'SIS%'
        AND CONVERT(CHAR(11), TH.TRANS_DATE, 103) BETWEEN @BasTar
                                                  AND     @Bittar

So because I use ST.ID IN(SELECT ID FROM FLO_AA_MAGAZALAR) in the case statement, it gives me that error for each sum(case) statement.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Answer 1

I'm not completely sure what you're trying to do, and I don't have your schema so I can't test this, but you might be able to move the SUMming out of the main query, here's an example using a CTE;

DECLARE @DATE AS SMALLDATETIME ,
    @BasTar DATE ,
    @Bittar DATE 

SET @DATE = GETDATE() - 1
SET @BasTar = GETDATE() - 1
SET @Bittar = GETDATE() - 1

; WITH CTE AS (
SELECT  ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END, 0) AS SATIST ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END, 0) AS SATISIV ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.DEFAULT_RETURN_PRICE )
                   END, 0) AS IADET ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                        THEN ( TS.VAT_TOTAL )
                   END, 0) AS IADEIV ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 0, 1, 3, 4, 5 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END, 0) AS SATISMIK ,
        ISNULL(CASE WHEN TH.PTYPE IN ( 2, 6 )
                             AND ST.ID IN ( SELECT  ID
                                            FROM    FLO_AA_MAGAZALAR )
                             AND TH.STATUS = 0 THEN TS.AMOUNT
                   END, 0) AS IADEMIK
FROM    TRANSACTION_HEADER TH WITH ( NOLOCK )
        INNER JOIN TRANSACTION_SALE TS WITH ( NOLOCK ) ON TS.FK_TRANSACTION_HEADER = TH.ID
        INNER JOIN STORE ST WITH ( NOLOCK ) ON ST.ID = TH.FK_STORE
                                               AND ST.NUM BETWEEN '3999' AND '9999'
WHERE   TH.STATUS = 0
        AND ST.DESCRIPTION NOT LIKE 'POL_%'
        AND ST.DESCRIPTION NOT LIKE 'SIS%'
        AND CONVERT(CHAR(11), TH.TRANS_DATE, 103) BETWEEN @BasTar
                                                  AND     @Bittar
)
SELECT
    SUM(SATIST) AS SATIST,
    SUM(SATISIV) AS SATISIV,
    SUM(IADET) AS IADET,
    SUM(IADEIV) AS IADEIV,
    SUM(SATISMIK) AS SATISMIK,
    SUM(IADEMIK) AS IADEMIK
FROM
    CTE