when i run below code in postgresql it give error but when i run this in mysql editor then it runs sucessully . what is reason behind it?

Question

this is my code when i run it on mysql it runs successfully but when i run on Postgres it doesn't run and give error as total_rate column doesn't exist:

create table gross ( 
id  numeric primary key , 
name text ,
rate numeric ,
quantity numeric
);
 insert into gross values ( 1 ,'bhindi os' , 10 , 5);
 insert into gross values ( 2 , 'palak' , 15 , 4);
 insert into gross values ( 3 , 'paneer' , 20 , 2);
 insert into gross values ( 4 , 'paneer' , 18 , 2);
 insert into gross values ( 5 , 'paneer' , 13 , 2);
-- select * from gross where  quantity >3 order by quantity ;
-- select name ,  sum(quantity) from gross group by name ;
-- select SUM(quantity) from gross ;
-- select * from gross where name like '%pal%';
-- select name ,Sum(quantity) as total_order from gross  group by name order by name desc ;
 select name  , sum(rate) as total_rate from gross
 
 
 group by name
 having total_rate > 10;

enter image description here

It is standard SQL behaviour that using alias in a having clause is not possible. MYSQL ignores this rule - like it also ignores some others - but even in MYSQL, you should write proper SQL queries, i.e. write HAVING SUM(rate) > 10. — Jonas Metzler, Nov 18 '22 at 05:10

Negato · Answer 1 · 2022-11-18T05:08:53.487

0

MySQL supports aliased column names to be used inside a HAVING clause, PostgreSQL does not. So for your Postgres query, write sum(rate) again instead of total_rate:

select
  name,
  sum(rate) as total_rate
from
  gross
group by
  name
having
  sum(rate) > 10 -- call the aggregation again
;

edited Nov 18 '22 at 05:08

answered Nov 18 '22 at 05:07

Negato

1
1

when i run below code in postgresql it give error but when i run this in mysql editor then it runs sucessully . what is reason behind it?

1 Answers1