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

Posted on 2014-08-12
Last Modified: 2014-08-24
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
Question by:hojohappy
    LVL 34

    Expert Comment

    by:Brian Crowe
    FROM myTable
    WHERE Col1 + Col2 + Col3 + Col4 + Col5 > 100
    LVL 68

    Accepted Solution

        Col1+Col2+Col3+Col4+Col5 > 100


    SELECT ..., TotalValue, ...
    FROM dbo.tablename
        SELECT Col1 + Col2 + Col3 + Col4 + Col5 AS TotalValue
    ) AS assign_alias
        TotalValue > 100
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 68

    Expert Comment

    ... or, my preference in this case, use CROSS APPLY to generate the desired alias name.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now