mysql - Real world total sales SQL query -

mysql - Real world total sales SQL query -

i'm new sql , having difficulty solving problem.

'what total sales across products salespeople sell @ to the lowest degree 1 unit of each of 5 individual products highest sales unit? create sure query returns total sales dollars in descending order. consider sales take place on 6 finish months prior @target_date parameter.'

3 tables exist in db. salesperson (salespersonid,salesytd) salesorderheader (salesorderid,orderdate,shipdate) salesorderdetail (salesorderid,salesorderdetailid,orderqty,productid,unitprice)

this i'm @ far. need compile have 1 statement , create necessary revisions. please help!

to capture top 5 highest sales unit, next syntax should work:

select productid, sum(orderqty*unitprice) salesorderdetail grouping productid orderqty >=1 , count(productid) =5 order sum(orderqty*unitprice) desc limit 5;

for target_date parameter, think along these lines?

select salespersonid ‘sales representative’, salesytd ‘total sales’, target_date salesperson target_date between ‘01-dec-13’ , ’01-may-14’;

for top 5 highest sales, rather propose simplified

class="lang-sql prettyprint-override">select productid, sum(orderqty * unitprice) sales salesorderdetail grouping productid order sales desc limit 5

and 6 months prior @target_date

class="lang-sql prettyprint-override">where orderdate between date_sub(@target_date, interval 6 months) , @target_date

assuming fk salesorderdetail(salespersonid), can bring together tables , top 5 sales

class="lang-sql prettyprint-override">select p.* salesperson p bring together salesorderheader h on h.salespersionid = p.salespersionid bring together salesorderdetail d on d.salesorderid = h.salesorderid bring together (select productid, sum(orderqty * unitprice) sales salesorderdetail grouping productid order sales desc limit 5) t5 on t5.productid = d.productid h.orderdate between date_sub(@target_date, interval 6 months) , @target_date order p.salesytd desc

mysql sql join