CODEWITHSUNDEEP
sqloracle-database
Tommy Roy
Tommy Roy

Reputation: 13

for each line, list the sum of sales together with the average of thoses who sold more

i'm trying to answer this question: "For each salesman, list his/her sales together with the average sales for those salesman who sold more than he/she did.

select sum(p.price * d.qty)
from salesman s 
join invoices i on (i.salesman = s.salesman)
join detail d on (d.ino = i.ino)
join parts p on (p.pno = d.pno)
where ---------(
  select sum(p.price * d.qty)
  from salesman s 
  join invoices i on (i.salesman = s.salesman)
  join detail d on (d.ino = i.ino)
  join parts p on (p.pno = d.pno)
)
group by s.salesman
order by s.salesman

i dont know how to compare every other sales with the one i got from the current row, and repeat that for every salesman

salesman is linked by some tables to reach the cost and the quantity sold of the parts

thank you for your help

Upvotes: 1

Views: 36

Answers (2)

user11173430
user11173430

Reputation: 1

try creating a temporary table #TempSales with the total sales for each saleman the # will create a temporary table in the tempdb database and will be dropped when the users session is closed if you do not drop it yourself you can run the entire query below in one transaction.

{ select s.salesman,sum(p.price * d.qty) as TotalSales into #TempSales from salesman s join invoices i on (i.salesman = s.salesman) join detail d on (d.ino = i.ino) join parts p on (p.pno = d.pno) group by s.saleman

select s.Salesman,TotalSales,(select avg(TotalSales) from #TempSales t where t.salesman <> s.salesman and t.TotalSales > s.TotalSales) as AverageSales
from #TempSales
order by s.salesman
drop table #TempSales

}

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You start with the sales for each sales person:

select i.salesman, sum(p.price * d.qty)
from invoices i join
     detail d
     on d.ino = i.ino join
     parts p
     on p.pno = d.pno
 group by i.salesmane;

Then, you can get your average using window functions:

select i.salesman,
       sum(p.price * d.qty),
       avg(sum(p.price * d.qty)) over (order by sum(p.price * d.qty) rows between 1 following and unbounded following)
from invoices i join
     detail d
     on d.ino = i.ino join
     parts p
     on p.pno = d.pno
group by i.salesmane;

Upvotes: 1

Related Questions