Query database by date, month, year

Question

How can I query order by date.

Like this:

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

Answer 1

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.