How can I query order by date.

Like this:

enter image description here

And the same for week, month, year...?

  • 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. – JNK Feb 22 '12 at 13:07
  • i have just updated the questions. I just need a statistics report – vNext Feb 22 '12 at 13:10
  • Well you still don't show what your actual data looks like, so I'll have to guess. – JNK Feb 22 '12 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 Bickerton Feb 22 '12 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 – vNext Feb 22 '12 at 13:26

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

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

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.

  • For days, how can I get last 30 days? not all days! – vNext Feb 22 '12 at 13:44
  • Add a WHERE filter - WHERE OrderDate > DATEADD(day, -30, GETDATE()) – JNK Feb 22 '12 at 13:45
  • 3
    Also you're welcome. You realize people who answer your question are just people on the internet who aren't getting paid or anything, right? – JNK Feb 22 '12 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.