How can I do this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can I do this

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2015-01-27 : 13:17:57

I get this error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

On my where clause:


WHERE  ( ( t1.archived = 0 ) 
         AND ( t1.recordnum LIKE 12345%' ) 
         -- COALESCE(Sum(CASE WHEN t9.approvedest=1 THEN [t8].[ctrvalue] ELSE 0 END),0) +  COALESCE(Sum(CASE WHEN t9.approvedest=1 THEN [t8].[govvalue] ELSE 0 END), 0)  
         --AND  (COALESCE(Sum(CASE WHEN t12.approvedest = 1 THEN [t11].[ctrvalue] ELSE 0 END),0) + COALESCE(Sum(CASE  WHEN t12.approvedest = 1 THEN [t11].[govvalue] ELSE 0 END), 0) >1) 
         AND ( COALESCE(CASE 
                          WHEN t12.approvedest = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) >= 450 ) 
         AND ( COALESCE(CASE 
                          WHEN t12.approvedest = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) <= 2147483647 ) ) 
GROUP  BY [t1].[id], 
          [t1].[idnum], 
          [t1].[recordtypeid], 
          [t1].[title], 
          [t3].[name], 
          [t4].[name], 
          t1.recordnum

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-27 : 13:47:26

Logically, WHERE clause is evaluated before the aggregations are performed, so you cannot use an aggregate function in the WHERE clause. HAVING clause is evaluated after the aggregations, so you can do the same thing in a HAVING clause - something like this:

WHERE  ( ( t1.archived = 0 ) 
         AND ( t1.recordnum LIKE '12345%' ) 
         -- COALESCE(Sum(CASE WHEN t9.approvedest=1 THEN [t8].[ctrvalue] ELSE 0 END),0) +  COALESCE(Sum(CASE WHEN t9.approvedest=1 THEN [t8].[govvalue] ELSE 0 END), 0)  
         --AND  (COALESCE(Sum(CASE WHEN t12.approvedest = 1 THEN [t11].[ctrvalue] ELSE 0 END),0) + COALESCE(Sum(CASE  WHEN t12.approvedest = 1 THEN [t11].[govvalue] ELSE 0 END), 0) >1) 
GROUP  BY [t1].[id], 
          [t1].[idnum], 
          [t1].[recordtypeid], 
          [t1].[title], 
          [t3].[name], 
          [t4].[name], 
          t1.recordnum
HAVING
         ( COALESCE(CASE 
                          WHEN t12.approvedest = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) >= 450 ) 
         AND ( COALESCE(CASE 
                          WHEN t12.approvedest = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) <= 2147483647 ) )

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-27 : 17:25:43

Within the HAVING clause, I think you have to use a group function on all referenced columns:


HAVING
         ( COALESCE(CASE 
                          WHEN MAX(t12.approvedest) = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) >= 450 ) 
         AND ( COALESCE(CASE 
                          WHEN MAX(t12.approvedest) = 1 THEN Sum([t11].[govvalue]) 
                                                        + Sum([t11].[ctrvalue]) 
                          ELSE 0 
                        END, 0) <= 2147483647 ) )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources