In SQL, why can't I use the other queries in SELECT statement to do calculation?

Question

Here's the sample table 'test0608':

product_name     color        amount
Product1         Red             123
Product1         Blue             126
Product2         Blue             103
Product2         Red             NULL
Product2         Red             89
Product1         Red             203

I'm trying to find all products with red ones more than blue ones. And here's my code:

        select 
         product_name
         ,sum(case when color = Blue then 1 else 0 end ) as blue_num 
         ,sum(case when color = Red then 1 else 0 end ) as red_num 
         ,(red_num- blue_num) as difference
         
         from test0608   
         group by product_name

However, I get the error 'Unknown column 'red_num' and 'blue_num'' which means I CAN'T use the two sums I just calculated in the select statement. Why?

I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Aug 19 '21 at 06:18
For Postgres see here: https://stackoverflow.com/questions/66603783/select-as-column-does-not-exist-in-where-clause or here: https://stackoverflow.com/questions/66392048/cant-find-column-alias-in-case-with-order-by-in-postgres — , Aug 19 '21 at 06:18
You cant use it like that. It is not allowed. See the below answers for alternatives — Jp Vinjamoori, Aug 19 '21 at 07:52
You need single quotes around string constants. I'm voting to close as a typo. — Gordon Linoff, Aug 19 '21 at 11:28

Rahul Biswas · Answer 1 · 2021-08-19T12:13:51.137

Please try this. blue_num and red_num are alias here. So it is not used here as a field.

SELECT  product_name
     , SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END) AS blue_num 
     , SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) AS red_num 
     , (SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) - SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END)) AS difference
FROM test0608   
GROUP BY product_name;

Alternate way by using subquery. if want to show positive difference of red_num then use red_num >= blue_num in WHERE clause. But if show all product but negative difference is 0 then USE CASE WHEN t.red_num >= t.blue_num THEN t.red_num - t.blue_num ELSE 0 END difference

SELECT t.product_name
     , t.blue_num
     , t.red_num
     , (t.red_num - t.blue_num) AS difference
FROM (SELECT product_name
           , SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END) AS blue_num 
           , SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) AS red_num
      FROM test0608   
      GROUP BY product_name) t;

Can also be written as `SUM(CASE color WHEN 'Red' THEN 1 WHEN 'Blue' THEN -1 ELSE 0 END) as difference`. — jarlh, Aug 19 '21 at 08:06

Thorsten Kettner · Answer 2 · 2021-08-19T06:29:20.937

There is no order in which the DBMS must evaluate the columns in a SELECT clause. The DBMS may try to evaluate difference before blue_num.

Hence alias names that you create in the SELECT clause are only known afterwards, i.e. in the ORDER BY clause and the query result. They can not be used inside the SELECT clause itself.

(Additional remark: Contrary to standard SQL, MySQL and PostgreSQL allow using the column aliases in the GROUP BY clause, too.)

In SQL, why can't I use the other queries in SELECT statement to do calculation?

2 Answers2

Linked