Link to home
Start Free TrialLog in
Avatar of Peter Nordberg
Peter NordbergFlag for Sweden

asked on

Sql group by orderdate and ordertotal

Hi,

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)

Open in new window


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

Open in new window


Can someone help me get it right so that I get the sum of sales for that particular date.

Thanks for help!

Peter
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

For starters, why are we taking a perfectly good date column (o.orderDate) and converting it to a character value, much less the unicode nvarchar?  Last I remember dates never have a Unicode character in them.

imo better to just leave it as a date, or if it's a datetime then CAST(o.orderDate as date).
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.