Peter Nordberg
asked on
Sql group by orderdate and ordertotal
Hi,
I want to group ordersales by date. I've formed this query:
For some reason the each instance comes in the result like below:
Can someone help me get it right so that I get the sum of sales for that particular date.
Thanks for help!
Peter
I want to group ordersales by date. I've formed this query:
SELECT CONVERT(NVARCHAR(10), o.orderDate, 121) AS date, SUM(o.OrderTotal) AS sales FROM dbo.Orders AS o
INNER JOIN dbo.aspnet_ProfileCustomized AS apc ON apc.sellerID = o.sellerID
WHERE o.saved = 1 AND apc.sellerID = 264 AND MONTH(o.orderDate) = 5 AND YEAR(o.orderDate) = 2017
GROUP BY CONVERT(NVARCHAR(10), o.orderDate, 121), o.OrderTotal
ORDER BY CONVERT(NVARCHAR(10), o.orderDate, 121)
For some reason the each instance comes in the result like below:
2017-05-02 937,50
2017-05-03 2500,00
2017-05-03 4987,50
2017-05-03 9987,50
2017-05-04 5625,00
2017-05-04 4375,00
2017-05-04 4987,50
2017-05-05 1237,50
2017-05-05 1250,00
2017-05-08 6250,00
2017-05-09 1875,00
2017-05-10 1237,50
2017-05-10 4375,00
2017-05-10 6250,00
2017-05-12 3750,00
2017-05-15 1000,00
2017-05-15 1250,00
2017-05-16 1000,00
2017-05-16 1250,00
2017-05-17 1000,00
2017-05-19 2500,00
2017-05-19 12375,00
2017-05-19 6250,00
Can someone help me get it right so that I get the sum of sales for that particular date.
Thanks for help!
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try changing your join to a RIGHT JOIN to make sure only data relevant to your seller is retrieved. The INNER JOIN will also include all rows from the left table where the date equals your condition.
imo better to just leave it as a date, or if it's a datetime then CAST(o.orderDate as date).