Aggregate function in where clause error

1

SELECT DISTINCT customer FROM office
WHERE country = "india" AND measure = "volume" AND sales_unit="10 pack" AND (SELECT sum(amount) FROM office)>=18.

Here the second select condition "(SELECT sum(amount) FROM office)>=18" not working. I am not getting these customers whose sum of amount is >=18.

2

hi

hope this helps

create sample data

drop table if exists #Office
create table #Office(customer varchar(10) , country varchar(10) , measure varchar(10) ,sales_unit varchar(10), amount int )
insert into #Office select 'Abc Corp', 'india', 'volume', '10 Pack' , 10
insert into #Office select 'Abc Corp', 'india', 'volume', '10 Pack' , 20
insert into #Office select 'HDFC Bank', 'india', 'volume', '9 Pack' , 10
insert into #Office select 'HDFC Bank', 'india', 'volume', '10 Pack' , 20
insert into #Office select 'Samsung', 'india', 'volume', '9 Pack' , 100
insert into #Office select 'Samsung', 'india', 'volume', '10 Pack' , 5
insert into #Office select 'Samsung', 'india', 'volume', '9 Pack' , 200
insert into #Office select 'Samsung', 'india', 'volume', '10 Pack' , 5

select * from #Office

SELECT 
    customer 
FROM 
   #Office
WHERE 
   country = 'india' AND measure = 'volume' AND sales_unit='10 pack' 
GROUP BY 
    customer
HAVING 
    sum(amount) >= 18

image