WHERE vs. HAVING - 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)

WHERE vs. HAVING

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2011-04-15 : 18:34:13

I must be dumb. I'm having some problems with particular syntax of an UPDATE query. Here is what i want to accomplish:

UPDATE Table1 SET Field1 = '1', Field2 = getdate()
WHERE Sum(Inventory) > '0' AND Ratio of sales to inventory < 0.05

Here is the actual syntax I have:

UPDATE Master_Child SET AllocateAlternate1 = '1', AllocateAlternateDate = Getdate()
WHERE SUM(ISNULL(OS_CurrentOnhand,0) + ISNULL(AZ_CurrentOnhand,0) + ISNULL(EB_CurrentOnhand,0) + ISNULL(NB_CurrentOnhand,0)) > '0' AND
((SUM(ISNULL(OS_30D,0) + ISNULL(AZ_30D,0) + ISNULL(EB_30D,0) + ISNULL(NB_30D,0))/SUM(ISNULL(OS_CurrentOnhand,0) + ISNULL(AZ_CurrentOnhand,0) + ISNULL(EB_CurrentOnhand,0) + ISNULL(NB_CurrentOnhand,0))) < 0.05)

I'm getting "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."

Any help out there?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-15 : 19:31:24

The error message pretty much describes what the problem is and what you need to do. You cannot have an aggregate function such as SUM, MIN etc. in a WHERE clause, unless it is in a subquery.

In your query, I assume you are trying to update those rows which when added together based on some condition would meet some criteria. Here is an example of how to do something similar.Here I am trying to update the third column based on an aggregation condition.

CREATE TABLE #tmp (id INT, s INT, u int);
INSERT INTO #tmp VALUES (1,11,NULL);
INSERT INTO #tmp VALUES (1,7,NULL);
INSERT INTO #tmp VALUES (2,8,NULL);

UPDATE t SET 
   u = 1
FROM
   #tmp t 
WHERE 
   exists
   (
      SELECT * 
      FROM #tmp t2 
      WHERE t.id = t2.id 
      GROUP BY t2.id 
      HAVING SUM(t2.s) > 12
   );
DROP TABLE #tmp;

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-18 : 16:56:50

you cant use aggregate function in where clause,if you want to use then use having,aggreate functions are in sql server mentioned in the link below

http://msdn.microsoft.com/en-us/library/ms173454.aspx

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

benking9987
Posting Yak Master

124 Posts

Posted - 2011-04-19 : 17:36:11

Thank you sunitabeck. That solution seemed to work perfectly. i was getting hung up on the particular syntax.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 19:37:04

Quite welcome! Take care.

Sunita.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources