I am trying to get both YTD totals and monthly totals from the same fields in one select statement and am having troubles. I need the monthly and YTD totals for both Order_Amnt and Commission_Amnt. Hope this makes sense. Thanks for any help anyone can provide.
Select Product_ID as ProductName,
Sum(Order_Amnt) As Amt_Ordered_Month,
Sum(Commission_Amnt) As Commission_Month
--need the same sums as above for YTD for Order_Amnt and Commission_Amnt
From Orders o
join SalesReps s on s.SalesRepNo=o.Order_SalesRep_No
join wo_Sales_Product_Families s on s.Product_Family_NO=o.Ord_Product_Family
Where o.Ord_Prd_Type = 'ABC')
and o.Order_Commission_Date Between '04/1/2016' and '04/03/2016'
--need an additional where clause here to get the YTD, something like this:
[and o.Order_Commission_Date Between '01/1/2016' and '04/03/2016']
Group By Product_ID , Order_Product_Type
Order By Product_ID , Order_Product_Type