Use a calculated column in a where clause

Question

I'm trying to use a calculated column in a where clause.

I've been trying everything from CROSS APPLY, to sub-query select but it does not give me anything near what I need.

My query so far:

SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c 
    LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
    LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
    LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
WHERE Sales > 100
GROUP BY p.Code, c.AccountNumber, Sales

This does not work, as 'Sales' is an invalid column

Can you use `HAVING (SUM(p.UnitPrice) * SUM(od.QtyShipped)) > 100` instead of the `WHERE`? (see http://www.w3schools.com/sql/sql_having.asp) — Jonny, Dec 16 '14 at 11:18

StuartLC · Accepted Answer · 2021-12-13T13:03:48.110

Using Derived Columns in a predicate

You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE clause (Also, note SUM() is specified just once, using the results of the multiplication):

SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
  SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
  FROM [dbo].Customer c 
      LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
      LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
      LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
  GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;

Repeating the Derived Column in a HAVING clause

As per @Jonny's comment, the other way is not to DRY up the calculated column, but to instead repeat the calculation. Use HAVING instead of WHERE after a GROUP BY has been applied.

SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
FROM [dbo].Customer c 
  LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
  LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
  LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
HAVING SUM(p.UnitPrice * od.QtyShipped) > 100;

In either case, as per comments below, note that the calculated expression is SUM(p.UnitPrice * od.QtyShipped) and not SUM(p.UnitPrice) * SUM(od.QtyShipped).

hi stuart, i get: Msg 130, Level 15, State 1, Line 4 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. sql is not my thing so have no idea what this message means :$ — JadedEric, Dec 16 '14 at 11:27
The Sales computation should just be this : `SUM(p.UnitPrice * od.QtyShipped)` — StuartLC, Dec 16 '14 at 11:28
i see what you did, thank you. and so i'm learning as well :). thank you. — JadedEric, Dec 16 '14 at 11:29
What is the performance effect if you repeat the derived column in a having clause compared to wrapping it? — kamaci, Apr 26 '22 at 01:51

score 10 · Answer 2 · edited Nov 22 '20 at 02:01

You can use the common table expression for this

;WITH CTE AS
    (
    SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c 
        LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
        LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
        LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
    
    GROUP BY p.Code, c.AccountNumber, Sale
    
    )
    
SELECT *
FROM CTE WHERE CTE.Sales>100

score -5 · Answer 3 · answered Dec 16 '14 at 11:20

-5

If it's a calculated column you can use "HAVING".

SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c 
    LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
    LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
    LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber, Sales
HAVING SALES > 100;

answered Dec 16 '14 at 11:20

Muran

67
3

7

Sadly, not. You can use a column alias in the `ORDER BY`, but not in a `HAVING` - you'll either need to repeat the full calc in the HAVING (like @Jonny's comment), or wrap the computed column. [Demo Here](http://sqlfiddle.com/#!6/a8edb/2) – StuartLC Dec 16 '14 at 11:27

Use a calculated column in a where clause

3 Answers3

Linked

Related