[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

SQL Server Select to Add column values and then use results in where clause

I need a SQL Server Select that will add up multiple table columns and then use the results in a where clause.

Col1    Col2    Col3   Col4    Col5
100     99.99   4.00   500    4.00
4          0.99     3.33   9.99   10.00

I need to find all the rows with a total value of Col1 - Col5 greater 100.

Select *  where TotalValue > 100
0
hojohappy
Asked:
hojohappy
  • 2
1 Solution
 
Brian CroweCommented:
SELECT...
FROM myTable
WHERE Col1 + Col2 + Col3 + Col4 + Col5 > 100
0
 
Scott PletcherSenior DBACommented:
WHERE
    Col1+Col2+Col3+Col4+Col5 > 100

Or:

SELECT ..., TotalValue, ...
FROM dbo.tablename
CROSS APPLY (
    SELECT Col1 + Col2 + Col3 + Col4 + Col5 AS TotalValue
) AS assign_alias
WHERE
    TotalValue > 100
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You cannot use the expression alias in the where clause (only in the order by)
Either you repeat  the expression, use a subquery/inline query, or you could add that column directly as computed column to the table.
0
 
Scott PletcherSenior DBACommented:
... or, my preference in this case, use CROSS APPLY to generate the desired alias name.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now