Select and sums from another table. Whats wrong with this SQL?

Whats wrong with this SQL?

SELECT Id, (select SUM(VALUE) f开发者_如何学JAVArom SomeTable) AS SumValue, GETDATE()
FROM MyTable
WHERE SumValue > 0

You cannot use aliased columns in the SELECT clause in the same query, except in ORDER BY.

It needs to be subqueried

SELECT Id, SumValue, GETDATE()
FROM (
    SELECT Id, (select SUM(VALUE) from TABLE) AS SumValue
    FROM MyTable
) X
WHERE SumValue > 0

That is the general case. For your specific query, it doesn't make sense because the subquery is not correlated to the outer query, so either NO rows show, or ALL rows show (with the same SumValue). I will simply assume you have simplified the query a lot since a table name of "table" doesn't really work.

I would probably rewrite like this:

SELECT a.Id,  b.SumValue, GETDATE() as [now]
FROM MyTable a
Join 
(
select id, SUM(VALUE) as [SumValue]
from [TABLE]
Group by id
)b on a.Id = b.Id
WHERE b.SumValue > 0

This is assuming that the value you are totalling relates to the ID in your table?

right way is

SELECT Id, (select SUM(VALUE) from TABLE) AS SumValue, GETDATE()
FROM MyTable
WHERE (select SUM(VALUE) from TABLE) > 0