How can I query order by date.
Like this:
And the same for week, month, year...?
6
-
What is your table structure like? You need to make it very clear what your data looks like, and what your desired results are. right now it's not clear at all. You can edit your question to add that info in.– JNKCommented Feb 22, 2012 at 13:07
-
i have just updated the questions. I just need a statistics report– vNextCommented Feb 22, 2012 at 13:10
-
Well you still don't show what your actual data looks like, so I'll have to guess.– JNKCommented Feb 22, 2012 at 13:21
-
1-1 as this looks like a basic homework questions that a simple google search will answer (or a basic knowledge of sql)– Andrew BickertonCommented Feb 22, 2012 at 13:21
-
I need a report to show many orders in database last 30 days. Ex: 02/22/2012 -> 132 orders, 02/21/2012 -> 81 orders... etc. Just show date, total orders of that day. That's all– vNextCommented Feb 22, 2012 at 13:26
|
Show 1 more comment
1 Answer
Without sample data I need to guess, but you want aggregations.
For Days:
SELECT CAST(OrderDate as DATE), as 'Date'
COUNT(*) as 'Orders',
SUM(OrderPaidAmt) as 'TotalAmpunt'
FROM OrderTable
GROUP BY CAST(OrderDate as DATE)
For weeks or months, you need to do aggregation on DATEPART output:
SELECT DATEPART(WEEK, OrderDate) as 'Week',
COUNT(*) as 'Orders',
SUM(OrderPaidAmt) as 'TotalAmpunt'
FROM OrderTable
WHERE YEAR(Orderdate) = YEAR(GETDATE())
GROUP BY CAST(OrderDate as DATE)
I added the WHERE clause in the second one to only show from this year. If you want to see multiple years you need to GROUP BY the YEAR() value of your orderdate as well.
