Denni S Denni S - 6 months ago 15
SQL Question

how to show agregate on where clause ? or is there any other way to solve this

the continue for my question.

I want to show agregate function on where clause. but an error occured.

my table :

enter image description here

I have tried like this (which method is to show total purchase for sales_id after where clause.

select * from customer where customer_id = ANY (
select customer_id from sales where sales_id = ANY (
select sales_id from sales_detail where sum(total) < (
select sum(total) from sales_detail where sales_id = (
select sales_id from sales where customer_id = (
select customer_id from customer where name = 'steven'
)
)
) group by sales_id
)
)


and I have tried like this (which the number of purchase was within sub query)

select * from customer where customer_id = ANY (
select customer_id from sales where sales_id = ANY (
select sales_id from sales_detail where (
select sum(total) from sales_detail where sales_id = (
select sales_id from sales where customer_id = (
select customer_id from customer where name = 'steven'
)
)
)
< ANY (
select sum(total) as number_of_purchase, sales_id /*, sales_id*/ from sales_detail group by sales_id
) group by sales_id
)
)


I have show the number of purchases for those 5 sales id

select sum(total) as number_of_purchase, sales_id /*, sales_id*/
from sales_detail
group by sales_id


and the result was like this

enter image description here


what I want is


I want to show 'costumer name' and 'costumer id' where the 'number of purchase' is more than 'the number of purchase' of 'steven'

number of purchase of steven is 3

so the result should be like this

| name | customer_id|
| Clark | c03 |
| Josh | c05 |


because total purchase all of them was more than 3
you can look from result above

thx.

Answer
select customer_id , name ,sum(total)
from customer ,sales_detail d, Sales s 
where d.sales_id = s.sales_id
and  customer_id = s.customer_id
having sum(total) > ( select sum(total)
                      from customer , sales s, sales_detail d
                      where customer_id = s.customer_id
                      and d.sales_id = s.sales_id 
                      and name = 'Steven')
Comments