alias will make more sense in the final query
step1: useful columns
actual price of single unit = price - price * discount/100
total price of unit grouped = sum((price - price * discount/100) * quantity)
step 2: Average sales amount
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
step 3: Table for those saleid who have more than avg sale amount
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
)
step 4: selecting saleid from table
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
))
step 5: selecting sid according to saleid
select s2.sid from sale s2 where s2.saleid in(
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
)))
step6: final query with all the data asked
select s1.sid,s1.sname,s1.location from salesman s1 where s1.sid in (
select s2.sid from sale s2 where s2.saleid in(
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
))))
alias will make more sense in the final query
step1: useful columns
actual price of single unit = price - price * discount/100
total price of unit grouped = sum((price - price * discount/100) * quantity)
step 2: Average sales amount
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
step 3: Table for those saleid who have more than avg sale amount
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
)
step 4: selecting saleid from table
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
))
step 5: selecting sid according to saleid
select s2.sid from sale s2 where s2.saleid in(
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
)))
step6: final query with all the data asked
select s1.sid,s1.sname,s1.location from salesman s1 where s1.sid in (
select s2.sid from sale s2 where s2.saleid in(
select saleid from (
select s3.saleid,sum((s4.price - s4.price * s4.discount/100) * s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid
group by s3.saleid having sum((s4.price - s4.price * s4.discount/100) * s3.quantity) > (
select avg(sum((s6.price - s6.price * s6.discount/100) * s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid
))))