CODEWITHSUNDEEP
sql-serversubqueryexpressioncaseaggregate-functions
litpost
litpost

Reputation: 105

Cannot perform an aggregate function on an expression containing subquery

I am trying to include subquery in SELECT statement but get error:

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

DECLARE @ShN money=STRLOG.dbo.fn_VarValue('ShN') 
DECLARE @ShNs money=STRLOG.dbo.fn_VarValue('ShNs') 
DECLARE @ShNN money=STRLOG.dbo.fn_VarValue('ShN') +STRLOG.dbo.fn_VarValue('ShNs') 
DECLARE @ShD money=STRLOG.dbo.fn_VarValue('ShD') 

    SELECT sum(
        CASE 
            WHEN GR2='33' 
            THEN @ShN

            WHEN GR2='3' 
            THEN 
                CASE 
                -- find if next day is day off          
                WHEN EXISTS (SELECT * FROM tbl_Holidays WHERE [HolidayDate]=DATEADD("DAY",1,'2014.11.30'))
                THEN @ShNs  
                ELSE @ShNN 
                END
            ELSE @ShD
        END
        )/60, 
        tbl_Tabel.EmplCodeID
        FROM tbl_TabelWHrs INNER JOIN tbl_Tabel ON tbl_TabelWHrs.TabWHrsID = tbl_Tabel.TabWHrsID
        WHERE (tbl_TabelWHrs.TabMon='2014.12' AND tbl_Tabel.EmplCodeID='7040023' AND GR2 is not null)
        GROUP BY tbl_Tabel.EmplCodeID;

Seems simple but I am very new to subqueries. Could you please somebody show me direction.

Upvotes: 0

Views: 2943

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Using inline queries is a bad practice in the most cases. There are no examples where you can not change it to the join, left join, subquery. And in 90% of the situations inline query will work worse.

Try to do something like that:

DECLARE @ShN money=STRLOG.dbo.fn_VarValue('ShN') 
DECLARE @ShNs money=STRLOG.dbo.fn_VarValue('ShNs') 
DECLARE @ShNN money=STRLOG.dbo.fn_VarValue('ShN') +STRLOG.dbo.fn_VarValue('ShNs') 
DECLARE @ShD money=STRLOG.dbo.fn_VarValue('ShD') 

    SELECT sum(
        CASE 
            WHEN GR2='33' 
            THEN @ShN

            WHEN GR2='3' 
            THEN 
                CASE 
                -- find if next day is day off          
                WHEN th.SomeKey IS NOT NULL
                THEN @ShNs  
                ELSE @ShNN 
                END
            ELSE @ShD
        END
        )/60, 
        tbl_Tabel.EmplCodeID
        FROM tbl_TabelWHrs INNER JOIN tbl_Tabel ON tbl_TabelWHrs.TabWHrsID = tbl_Tabel.TabWHrsID
        LEFT JOIN tbl_Holidays th
          ON [HolidayDate]=DATEADD("DAY",1,'2014.11.30')
        WHERE (tbl_TabelWHrs.TabMon='2014.12' AND tbl_Tabel.EmplCodeID='7040023' AND GR2 is not null)

        GROUP BY tbl_Tabel.EmplCodeID;

Upvotes: 1

Related Questions