Christine Christine - 1 year ago 74
MySQL Question

MS SQL getting the sum of given date

Hi guys im having a hard time on how to get the sum of a given date in TotalSold column. I have here 5 columns PartNumber, Desc, TotalOrder, TotalSold and POUnitPrice. How can i filter the date of TotalSold. Thanks!


select PartNumber = (select name from base_product as PROD where prod.prodid = POL.prodid),
TotalOrder = sum(POL.quantity),
TotalSold = (select sum(SOL.quantity) from so_salesorder_line as SOL where SOL.ProdId = POL.prodid),
PoUnitCost = max(POL.UnitPrice)
from PO_Purchaseorder_line as POL
where POL.purchaseorderid in (
select purchaseorderid
from PO_purchaseorder as PO
where orderdate >= '5/1/2015')
group by POL.description, POL.prodid

Answer Source

Join so_salesorder_line with so_salesorder so as to be able to access the date.

By the way, use ISO date literals instead of ambiguous dates strings. ('5/1/2015' could mean May 1 or January 5 depending on the current setting in your DBMS.)

Here is the part to get the TotalSold (for the 1st of May - change this to '20150105' if you want the 5th of January instead.)

TotalSold = 
  select sum(SOL.quantity) 
  from so_salesorder_line as SOL 
  join so_salesorder SO on SO.salesorderid = SOL.salesorderid
  where SOL.ProdId = POL.prodid
  and SO.orderdate >= '20150501'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download