Msg 147 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list

Question

I am running SSMS 2008

select 
    A.JOBNUMBR as [Service Call], A.PONUMBER as [PO Number], 
    sum(A.QTYUNCMTBASE*A.UNITCOST) as [Committed Cost PO], 
    SUM(B.WS_Committed_Cost) as [Committed Cost WS], 
    (sum(A.QTYUNCMTBASE*A.UNITCOST)-SUM(B.WS_Committed_Cost)) as [Variance]
from 
    POP10110 A
LEFT JOIN 
    SV_Costs B on A.JOBNUMBR = B.Service_Call_ID and A.PONUMBER = B.Reference_TRX_Number and B.WS_Committed_Cost <> 0
where 
    A.Product_Indicator = 3
    and A.QTYUNCMTBASE <> 0
    and (sum(A.QTYUNCMTBASE * A.UNITCOST) - SUM(B.WS_Committed_Cost)) <> 0
group by 
    A.JOBNUMBR, A.PONUMBER
order by 
    A.JOBNUMBR, A.PONUMBER

I get the error MSG 147 in the subject line when I run this query. The 3rd where clause is the same as the aggregated column [Variance] which I am trying to filter down to <> 0. The query runs without this 3rd where clause.

I have been doing a bit of reading and understand that I need to include HAVING or a select statement in the where clause but I can't quite get the syntax right, any help would be appreciated.

Thank you

Tania

Add the tag of the database you´re using on the question (seems like SQL Server). — Vitor Tyburski, May 06 '14 at 03:04

Vitor Tyburski · Accepted Answer · 2014-05-06T03:00:05.063

0

Sum is an aggregated column, invalid for use in your selection (WHERE clause). One solution is to wrap this on another SELECT. OR:

SELECT
          A.JOBNUMBR as [Service Call], 
          A.PONUMBER as [PO Number], 
          SUM(A.QTYUNCMTBASE*A.UNITCOST) as [Committed Cost PO], 
          SUM(B.WS_Committed_Cost) as [Committed Cost WS], 
          SUM(A.QTYUNCMTBASE*A.UNITCOST) - SUM(B.WS_Committed_Cost) as [Variance]
FROM      POP10110 A
LEFT JOIN SV_Costs B 
       ON A.JOBNUMBR = B.Service_Call_ID 
          AND A.PONUMBER = B.Reference_TRX_Number 
          AND B.WS_Committed_Cost <> 0
WHERE     A.Product_Indicator = 3
      AND A.QTYUNCMTBASE <> 0
GROUP BY  A.JOBNUMBR, A.PONUMBER 
   HAVING (SUM(A.QTYUNCMTBASE * A.UNITCOST) - SUM(B.WS_Committed_Cost)) <> 0
ORDER BY  A.JOBNUMBR, A.PONUMBER

edited May 06 '14 at 03:00

answered May 06 '14 at 02:53

Vitor Tyburski

1,062
13
17

Ah thankyou for this. I had actually tried that but put the HAVING before the GROUP BY or after the ORDER BY and neitehr worked. There is NULL results under Variance though, the <> 0 seems to be filtering these out too? I didn't expect that. Do you know how to keep the NULL results? – Tania Mofflin May 07 '14 at 03:24
I got around it with this.. having (sum(A.QTYUNCMTBASE*A.UNITCOST)-SUM(B.WS_Committed_Cost)) IS NULL or (sum(A.QTYUNCMTBASE*A.UNITCOST)-SUM(B.WS_Committed_Cost)) > 0 or (sum(A.QTYUNCMTBASE*A.UNITCOST)-SUM(B.WS_Committed_Cost)) < 0 Although I'm not sure that's the most efficient way but it works. – Tania Mofflin May 07 '14 at 05:31
Why not "(SUM(x) - SUM(y)) IS NULL OR (SUM(x) - SUM(y)) <> 0"? – Vitor Tyburski May 08 '14 at 02:37
And I think that there is no "more performatic" way to do it, as it is. What you could do is remodeling and get rid of the NULL "shit", so that you can remove the OR. – Vitor Tyburski May 08 '14 at 02:39
Thanks both your points are fairly obvious logic now that I see them, think it had been a long day. Will go with the second one. – Tania Mofflin May 12 '14 at 04:00

Msg 147 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list

1 Answers1