Having clause with subquery

user_14344018 0
Viewed 87

I have below scenario

CREATE TABLE plch_sales
(
   region      VARCHAR2 (100),
   product     VARCHAR2 (100),
   amount   NUMBER
)
/

INSERT INTO plch_sales VALUES ('North', 'Magic Wand',  1000);
INSERT INTO plch_sales VALUES ('North', 'Skele-Gro',   1000);
INSERT INTO plch_sales VALUES ('North', 'Timeturner ', 1000);
INSERT INTO plch_sales VALUES ('South', 'Portkey',     1000);
INSERT INTO plch_sales VALUES ('South', 'Quaffle',     1000);
INSERT INTO plch_sales VALUES ('West',  'Imperius',    1000);
INSERT INTO plch_sales VALUES ('West',  'Gringotts',   1000);
COMMIT;

why the below query is producing no rows? the database is oracle.

select region, sum(amount) sm_amount from PLCH_SALES group by region having sum(amount) > (select sum(amount)/3 from PLCH_SALES);
oracle sql
3 Answers

You need to first do the sum() and then divide it by 3 like the below -

SELECT region, SUM(amount) sm_amount
FROM PLCH_SALES
GROUP BY region
HAVING SUM(amount) > (SELECT SUM(amount)FROM PLCH_SALES)/3;

OR you can alternatively try using window() aggregate function

select distinct region,sm_amount from
(
select region,sum(amount) over(partition by region) as sm_amount, sum(amount) over()/3 as total
from plch_sales
)a where sm_amount>total
user_69148640

The way you have written will not work with having clause .

There are multiple ways to do it like with CTE , Parent_table or like below :

select region, sum(amount) sm_amount 
from PLCH_SALES 
group by region 
having sum(amount) >(sum(amount)/3 )

;

user_29788400

Your query should work. The subquery returns:

2333.333333333333333333333333333333333333

The query without the having returns:

REGION  SM_AMOUNT
West    2000
North   3000
South   2000

So clearly, 'North' should be in the result set.

However, it does not in Oracle. Here is a db<>fiddle. It does work with a subquery:

select x.*
from (select region, sum(amount) as sm_amount, (select sum(amount)/3 from PLCH_SALES) as thirds
      from PLCH_SALES
      group by region
     ) x
where sm_amount > thirds ;

And it does work with a CTE:

with thirds as (
      select sum(s2.amount)/3 as val from PLCH_SALES s2
     )
select region, sum(amount) sm_amount
from PLCH_SALES
group by region
having sum(amount) > (select val from thirds);

This sounds like a bug.

The query works in other databases:

And no doubt any other database where one would try it.

user_11440350
Related