Geek Answers Handbook

SQL query to calculate the total number of orders per day?

Can someone send an SQL query to calculate the total number 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 example, in February 2009 there are 2 orders.

  - 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 that will calculate and show the total amount per day. So for February 3, 2009 the total will be 699.98

I need to display the total order per day in a chart

If it would be easier to do this with PHP, specify it too.

UPDATE:
I would like to clarify that I need the Total amount for the day in the current month. I forgot to mention this in my original question.

So, I considered Peter's request to get the total number of orders + the total amount for the day in this month.

Please let me know if he needs any correction or if there is a better and shorter way to do this.

 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)
+6
sql php mysql charts
source share
5 answers

MySQL's date () function will return a DATEIME or TIMESTAMP value without any information about the hours / minutes / seconds - this means that you reduce the precision to a day value.

So, all you have to do is group, and then add your aggregate functions to the columns you need.

 SELECT date(order_placed_date) , COUNT(id) AS num_orders , SUM(order_total) AS daily_total FROM [Table] GROUP BY date(order_placed_date)
+9
source share
 SELECT date(order_placed_date) , COUNT(id) AS num_orders , SUM(order_total) AS daily_total FROM orders GROUP BY 1

Just copied Peter's answer, but modified it to make it work. Plus spun the group.

+1
source share

A group is your friend here. It can be grouped by grouped lines. Request example:

 SELECT order_placed_date, SUM(order_total) FROM orders GROUP BY order_placed_date

Of course, in your example, you probably want to extract only part of the day / month / year using the DATE () function, and a group about this.

0
source share

I don’t know what it is in MYSSQL, but in MSSQL it will be,

 select datepart(yyyy, order_placed_date), datepart(mm, order_placed_date), datepart(dd, order_placed_date), sum(order_total) from orders group by datepart(yyyy, order_placed_date), datepart(mm, order_placed_date), datepart (dd, order_placed_date)
0
source share
 select YEAR(datename(year,ORDER_DATE)) as YEARS, count(ORDER_nO) as [NO(ORDER)] from ProductOrder group by YEAR(datename(year,ORDER_DATE))
0
source share

More articles:


All Articles