CODEWITHSUNDEEP
sqlpostgresql
Daniel Chepenko
Daniel Chepenko

Reputation: 2268

SQL error: having operation

I need to write a sql script, that has to show all man client, whose maximum income twice bigger, than minimum income overall. 

SELECT
  DISTINCT
  customer_rk,
  max(monthly_income_amt),
  min(monthly_income_amt),
  max(monthly_income_amt) / min(monthly_income_amt) AS income_ratio
FROM asql.individual_customer
WHERE middle_nm LIKE '%ВИЧ'
GROUP BY customer_rk
HAVING income_ratio > 2;

middle_nm like '%ВИЧ' - it is for sorting men by their middle name (Russian language requirments)

Here is how table looks like:

enter image description here

A receives such error:

ERROR: column "income_ratio" does not exist (line 6)

What I am doing wrong?

Upvotes: 0

Views: 42

Answers (3)

jarlh
jarlh

Reputation: 44786

Wrap the query up in a derived table. Then you can put income_ratio in the WHERE clause:

select * from
(
    SELECT
      customer_rk,
      max(monthly_income_amt),
      min(monthly_income_amt),
      max(monthly_income_amt) / min(monthly_income_amt) AS income_ratio
    FROM asql.individual_customer
    WHERE middle_nm LIKE '%ВИЧ'
    GROUP BY customer_rk
) dt
where income__ratio > 2;

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

Need modification in your having clause as below. 

SELECT
      DISTINCT
      customer_rk,
      max(monthly_income_amt),
      min(monthly_income_amt),
      max(monthly_income_amt) / min(monthly_income_amt) AS income_ratio
    FROM asql.individual_customer
    WHERE middle_nm LIKE '%ВИЧ'
    GROUP BY customer_rk
    HAVING (max(monthly_income_amt) / min(monthly_income_amt)) > 2

Upvotes: 1

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

instead of income_ratio write in having clause as max(monthly_income_amt) / min(monthly_income_amt)>2 .

Also distinct does not make sense as you are grouping.

Upvotes: 2

Related Questions