Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query for Calculating Total No. of Orders per Day?

Can anyone post a SQL query for Calculating Total No. of Orders per Day?

Here are the Columns along with their data in my Database.


order_id    order_placed_date   order_total  
 - 1    12/30/2008 12:06:24 AM  2499.99  
 - 2    2/3/2009 1:57:17 AM 199.99  
 - 3    2/3/2009 1:58:27 AM 449.99  
 - 4    5/3/2009 1:58:48 AM 299.99  
 - 5    6/3/2009 2:00:31 AM 359.94
 - 6    6/3/2009 2:01:47 AM 279.97
 - 7    6/3/2009 2:02:31 AM 1359.94
 - 9    7/1/2009 2:21:18 PM 5099.98
 - 10   7/1/2009 2:21:36 PM 2621.97
 - 11   7/2/2009 2:22:18 PM 2169.95
 - 12   7/3/2009 2:23:29 PM 2249.95
 - 13   7/4/2009 2:24:24 PM 5509.95
 - 14   7/5/2009 12:15:17 AM    449.99
 - 15   7/5/2009 12:18:08 AM    2299.99
 - 16   7/5/2009 12:18:28 AM    3999.99
 - 17   7/5/2009 12:18:45 AM    1939.99
 - 18   7/5/2009 11:58:07 PM    39.99
 - 19   7/6/2009 12:00:42 AM    1899.99
 - 20   7/6/2009 12:01:00 AM    3999.99
 - 21   7/7/2009 12:06:38 AM    199.99
 - 22   7/7/2009 12:08:31 AM    1143.97
 - 23   7/7/2009 12:09:13 AM    449.99
 - 26   7/15/2009 1:30:03 PM    5469
 - 27   7/15/2009 2:14:24 PM    329.97
 - 28   7/15/2009 6:18:47 PM    5469
 - 29   7/15/2009 10:17:36 PM   39.99

For e.g. there are 2 orders in the month of Febuary 2009

 - 2    2/3/2009 1:57:17 AM     199.99  
 - 3    2/3/2009 1:58:27 AM     449.99  

I need a sql query which would calculate and show the total amount per day. So for 3rd of Feb 2009, the total would be 699.98

I need to display Total Order Amount per day in a Chart

If it would be easier to do it with PHP, do mention it as well.


UPDATE:
I would like to clarify that I needed the Total Amount Per Day in the Present Month. I forgot to mention that in my initial question.

So i udpated Peter's query to get Total No. of Orders + Total Amount per Day in this Month.

Please, let me know if it needs any correction or is there a better and shorter way of doing it.


SELECT date(order_placed_date), COUNT(order_id) AS num_orders, SUM(order_total) AS daily_total
FROM orders
WHERE order_placed_date>=date_sub(current_date, INTERVAL 31 DAY)
GROUP BY date(order_placed_date)
like image 395
Ibn Saeed Avatar asked Jul 15 '09 18:07

Ibn Saeed


1 Answers

MySQL's date() function will return a DATEIME or TIMESTAMP value without any hour/minute/second info - which means you reduce the accuracy to the day of the value.

So all you need to do is group by that and then add your aggregate functions to the right columns.

SELECT date(order_placed_date)
     , COUNT(id) AS num_orders
     , SUM(order_total) AS daily_total
  FROM [Table]
 GROUP BY date(order_placed_date)
like image 145
Peter Bailey Avatar answered Sep 28 '22 03:09

Peter Bailey