MySQL SUM grouped by Month,Year with date range from two tables

Question

I have three tables.

$sql1="SELECT * FROM user"; //PK user_id
$sql2="SELECT * FROM order";   //PK order_id , (order_user_id = user_id)        
$sql3="SELECT * FROM order_old"; //same content as `order` table

I need to SUM(order_amount) AS TOTAL_ORDER from order and order_old table and GROUP them by Month and Year with date range condition. Usually first two week of last month is still in order table.

I need to SUM the amount when there is records on order_old and order that has same month and year. If I use UNION , it would retrieve the data well but there would be a double month with values ready to be summed become one.

 $SQL="SELECT a.user_name, Sum(sum(b.order_amount)-sum(b.discount) + 
sum(c.order_amount)-sum(c.discount)) AS total_order, DATE_FORMAT(b.order_date,'%Y-%m') AS time 
FROM USER a INNER JOIN order_old b ON ( a.user_id = b.order_user_id ) 
INNER JOIN order c ON ( a.user_id = c.order_user_id ) 
WHERE a.user_id <> '' AND b.order_date BETWEEN '2014-09-01' AND '2014-11-31'
AND a.user_name LIKE 'JOHN%' 
AND c.order_date BETWEEN '2014-01-1' AND '2014-11-31'
 GROUP BY DATE_FORMAT(b.to_in_date,'%Y-%m') ASC"`

From above SQL ,I managed to get the value but it did not show the data from order table when the query condition is from January 2014 to November 2014. How do I select the data from two tables(order and order_old) when you need to apply the date range in these two tables?

USER

  user_id user_name
   --       ----------
   001        JOHN
   002        ADAM

ORDER

  order_id order_user_id     order_amount     order_date         discount
   --       ----------       ----------        ----------        --------- 
   6       001               100             01/11/2014 10:55     50
   7       002               100             01/11/2014 10:55     20
   8       001                50             25/10/2014 10:55      0
   9       001               100             23/10/2014 11:00      0
  10       002                50             21/10/2014 11:00      0

ORDER_OLD

  order_id order_user_id     order_amount     order_date         discount
   --       ----------       ----------        ----------        --------- 
   1        001               100             15/10/2014 10:55      50
   2        002               100             11/10/2014 10:55      20
   3        001               50              11/10/2014 10:55       0
   4        001               100             21/09/2014 11:00       0
   5        002               50              21/09/2014 11:00       0

Expected Result:

 No     user_name          Total Order     Time
 --       ----------       ----------      ---------       
 1        John               100            2014-09
 2        John               250            2014-10
 3        John                50            2014-11

Ankit Bajpai Ankit Bajpai · Accepted Answer · 2014-11-04T09:51:04

Try this:-

SELECT OO.ORDER_ID, U.USER_NAME, SUM(OO.ORDER_AMT)-SUM(OO.DISCOUNT) AS TOTAL ORDER, TIME
FROM ORDER_OLD OO, ORDER O, USER U
WHERE OO.ORDER_USER_ID = O.ORDER_USER_ID
AND U.USER_ID = OO.ORDER_USER_ID
AND U.USER_NAME = 'John'
OO.ORDER_DATE BETWEEN '2014-09-01' AND '2014-11-31' 
GROUP BY OO.ORDER_ID, U.USER_NAME, MONTH(ORDER_DATE);

MySQL SUM grouped by Month,Year with date range from two tables

1 Answers