CODEWITHSUNDEEP
sql-serversum
BohdanZPM
BohdanZPM

Reputation: 755

SQL Server - cannot select rows with Sum > 0

I'm using SQL Server. I have table with Hours values for different Tasks and days from 1 to 7, like so (table TaskHours):

        | TaskID | Hours1 | Hours2 | Hours3 | Hours4 | Hours5 | Hours6 | Hours7 |
        | 1      | NULL   | 10     | NULL   | NULL   | NULL   | NULL   | NULL   |
        | 2      | NULL   | NULL   | 0      | NULL   | NULL   | NULL   | NULL   |
        | 3      | 0      | NULL   | 0      | NULL   | 0      | 0      | NULL   |
        | 4      | 3      | 3      | 0      | 3      | NULL   | NULL   | 3      |
        | 5      | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   | NULL   |

I need just calculate the sum of hours for each TaskID and select only those of them that has the Sum of hours > 0. But when I try to write:

    SELECT TaskID, Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7,
       SUM(ISNULL(Hours1, 0) + ISNULL(Hours2, 0) + ISNULL(Hours3, 0) + ISNULL(Hours4, 0)
        + ISNULL(Hours5, 0) + ISNULL(Hours6, 0) + ISNULL(Hours7, 0)) AS TotalHours
    FROM TaskHours
    WHERE TotalHours > 0

I then get the error:

Invalid column name 'TotalHours'.

What do I do wrong, and how to fix it?

Upvotes: 0

Views: 70

Answers (2)

Jorge González
Jorge González

Reputation: 3148

To my understanding, the problem is that TotalHours still doesn't exists when you write the WHERE clause.

So here is my solution:

SELECT * FROM(
SELECT TaskID, Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7,
       (IFNULL(Hours1, 0) + IFNULL(Hours2, 0) + IFNULL(Hours3, 0) + IFNULL(Hours4, 0) + IFNULL(Hours5, 0) + IFNULL(Hours6, 0) + IFNULL(Hours7, 0))
       AS TotalHours FROM taskhours) as res WHERE res.TotalHours > 0;

Let me explain. This are called SubQueries. Which are queries inside queries. If you read the code that I just posted you'll notice the following structure.

SELECT * FROM (subquery) as fooName WHERE fooName.foo = 'something';

This structures forces 'subquery' to be completely resolved before the outer structure is called. So in your case:

SELECT * FROM(subquery) as res WHERE res.TotalHours > 0;

Now TotalHours exists since 'subquery' that has

(IFNULL(Hours1, 0) + IFNULL(Hours2, 0) + IFNULL(Hours3, 0) + IFNULL(Hours4,0) + IFNULL(Hours5, 0) + IFNULL(Hours6, 0) + IFNULL(Hours7, 0)) AS TotalHours

has been resolved.

Another thing. I removed the SUM() because you said:

"since I need just calculate the sum of hours for each TaskID and select only those of them that has the Sum of hours > 0."

SUM() will actually sum all the outcomes of all the rows of your output leaving you with the total hours of all the TaskIDs in a single row like so:

With SUM()

And to my understanding you need something like this: Without SUM()

Finally: IFNULL() is the equivalent of ISNULL() in MYSQL.

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can use this in subquery as below

SELECT * from (
    SELECT TaskID, Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7,
    ISNULL(Hours1, 0) + ISNULL(Hours2, 0) + ISNULL(Hours3, 0) + ISNULL(Hours4, 0)
    + ISNULL(Hours5, 0) + ISNULL(Hours6, 0) + ISNULL(Hours7, 0) AS TotalHours
    FROM TaskHours   ) a
WHERE a.TotalHours > 0

Upvotes: 4

Related Questions