Menu
I have a table with a collection of orders. The fields are:
customerName (text) DateOfOrder (datetime).I would like to show totals of orders per week per customer. I would like to have it arranged for the Friday of each week so that it looks like this:
all dates follow mm/dd/yyyy
"bobs pizza", 3/5/2010, 10
"the phone co",3/5/2010,5
"bobs pizza", 3/12/2010, 3
"the phone co",3/12/2010,11
Could somebody please show me how to do this?
Thanks
420
How Do You Group Data by Week in SQL Server? SQL Server provides a function called DATEPART() , which returns a specified part (year, quarter, month, week, hour, minute, etc.) of a specified date. ORDER BY DATEPART(week, RegistrationDate);
WEEK() function in MySQL is used to find week number for a given date. If the date is NULL, the WEEK() function will return NULL. Otherwise, it returns the value of week which ranges between 0 to 53. The date or datetime from which we want to extract the week.
When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
To group by date part, use the GROUP BY clause and the EXTRACT() function. Pass EXTRACT() the date parts to isolate.
Make a field which you can group by more easily.
SELECT COUNT(OrderID), WeekStart
FROM
(
SELECT *,
dateadd(week, datediff(day,'20000107',yourDate) / 7, '20000107') AS WeekStart
FROM Orders
) o
GROUP BY WeekStart;
20000107 is a known Friday.
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
