Counting total per day with my result in SQL

I have 50 rows/entrys in my table Orders. I have a column, that holds when the order is claimed at, named claimed_at.

The date in this field are in this format: 2011-10-03 07:07:33

This is in the format (yy/mm/dd time).

I also have a column called price, this price is how much they paid.

I would like to display totals per day.

So fo开发者_StackOverflow社区r 6 orders from the date 2011-10-03, it should take the 6 order's price value, and plus them together.

So I can display:

2011-10-03  -- Total: 29292 Euros
2011-10-02  -- Total: 222 Euros
2011-09-28  -- Total: 4437 Euros

How can i do this?

You need to use aggregate functionality of MySQL in conjuction with some DATE conversion.

SELECT DATE(claimed_at) AS day
    , SUM(price) AS total
FROM Orders
GROUP BY day

Create a new field say day with DATE_FORMAT(claimed_at,'%Y-%m-%d') AS day , sum the price and group by day so you will get the result you want.

Something like

SELECT DATE_FORMAT(claimed_at,'%Y-%m-%d') AS day,SUM(price) as total FROM orders GROUP BY day ORDER BY day DESC

Use grouping like this :

SELECT claimed_at, SUM(price) AS total 
FROM Orders 
GROUP BY claimed_at

You need to use function to get only date part(not time) in grouping. I dont know which function is used in mysql, in MSSQL it's CONVERT() function.

SELECT TRUNC(claimed_at), SUM(PRICE) 
FROM my_orders_table 
GROUP BY TRUNC(claimed_at)