Counting Orders for the Day

[ryushinyama]

I have a database with a lot of orders. I would like to do a count of the orders by day, amount per day, and so on. I have an orderdate column that is a datetime data type. How would i query it to group by just the date and not the time?

Thanks

[Motoma]

Welcome to theScripts.

SELECT
    CONVERT(CHAR(10),orderTime,110)  AS OrderDate,
    SUM(1) AS TotalOrders FROM ordersTable
GROUP BY
    CONVERT(CHAR(10),orderTime,110)
ORDER BY
    OrderDate

Hope this helps.

[ryushinyama]

Perfect, Thank you!

[ryushinyama]

Ok, to take this one step further...How would you take this data and split it up by payment type? So you would get total orders for each day for each payment type between the entered dates? The payment type column being paymenttype. Thanks again.

[iam_clint]

group by date

[ryushinyama]

Yes, it already groups by date. I need it to group by date as well as paymenttype. So we would have
4/1/2007 Credit card 70
4/1/2007 Paypal 38
4/1/2007 Money Order 5
4/2/2007 Credit card 68
4/2/2007 Paypal 25
4/2/2007 Money Order 7
...
...
...

Right now from this query all I get is

4/1/2007 113
4/2/2007 100
4/3/2007 150
...
...
...

[iam_clint]

 
SELECT
	CONVERT(CHAR(10),orderTime,110) AS OrderDate, paymenttype,
	SUM(1) AS TotalOrders FROM ordersTable
GROUP BY
	CONVERT(CHAR(10),orderTime,110), orderType
ORDER BY
	OrderDate, paymenttype

[ryushinyama]

I would like to think that I tried that, it is simple enough. Oh well I guess I was missing something. THANKS!!