CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dheeraj Patnaik
Dheeraj Patnaik

Reputation: 375

How to group orders by Date

I have a order details table and I want to find the sum of price by each date and display them as

For Ex

   count   sum            date 
    5      619.95        2015-11-19
    3      334.97        2015-11-18
    4      734.96        2015-11-18
    5      1129.95       2015-11-18

I have written the query for getting the count and sum as

select count(id), sum([price])
from [OrderDetails]
where [date]between '2015-10-29 05:15:00' and '2015-11-09 00:01:00'
group by datepart(day,[date])

But not able to achieve with date. How can it be done?

Upvotes: 1

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

It seems like your column called date has both a date and time component. I would suggest converting it to a date, for both the select and group by:

select count(id), sum(price), cast([date] as date) as thedate
from OrderDetails
where [date] between '2015-10-29 05:15:00' and '2015-11-09 00:01:00'
group by cast([date] as date) 
order by thedate;

Note: date is a poor name for a column, because it is the name of a built-in type.

Upvotes: 1

gmiley
gmiley

Reputation: 6604

You need to include what you are grouping on in the SELECT portion of your query:

select count(id), sum([price]), datepart(day,[date]) as [date]
from [OrderDetails]
where [date] between '2015-10-29 05:15:00' and '2015-11-09 00:01:00'
group by datepart(day,[date]);

Upvotes: 4

Related Questions