CODEWITHSUNDEEP
sqlsql-server
iLuPa
iLuPa

Reputation: 151

Using result of a SQL case to compare and return another case

I'm trying to compare the result of a sum which returns an amount, by using a case that would return the result of this comparison:

SELECT u.name, u.account,
    sum (case u.account
        when t.sent_to then t.quantity * -1
    else t.quantity end) as quantity_sent,
        case when quantity_sent > u.available then 'YES'
            else 'NO' end as analysis_result
from users as u inner join trans as t
on u.account in (t.sent_to, t.received_by)
group by u.name, u.account;

Whenever I call quantity_sent back, I get an "Invalid column name quantity_sent". Is there any other way I can use the result on this newly created column to compare inside a case?

Upvotes: 0

Views: 532

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Try this- 

SELECT 
u.name, u.account,
SUM (
    CASE u.account
        WHEN t.sent_to THEN t.quantity * -1
        ELSE t.quantity 
    END
) AS quantity_sent,
CASE 
    WHEN SUM (
        CASE u.account
            WHEN t.sent_to then t.quantity * -1
            ELSE t.quantity 
        END ) > u.available THEN 'YES'
        ELSE 'NO' 
END as analysis_result
FROM users as u 
INNER JOIN trans as t
ON u.account in (t.sent_to, t.received_by)
GROUP BY u.name, u.account;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You need to repeat the expression or use a subquery:

select name, account, quantity_sent,
       (case when quantity_sent > u.available then 'YES'
             else 'NO'
        end) as analysis_result
from (select u.name, u.account,
             sum(case u.account
                     when t.sent_to then t.quantity * -1
                     else t.quantity
                 end) as quantity_sent
      from users u inner join
           trans t
           on u.account in (t.sent_to, t.received_by)
      group by u.name, u.account
     ) u;

This is part of the general rule in SQL that you cannot re-use a column alias in the SELECT where it is defined. There is a simple reason for this: SQL does not guarantee the order of evaluation of the expressions in the SELECT.

Upvotes: 1

Related Questions