Want to get sum of 2 different columns from 2 different tables and want to display final sum

Question

SELECT 
    SUM(p.price + c.price) AS product_weekly_total 
FROM t_product_purchase p
LEFT JOIN 
    t_coupon_purchase c on c.storepkid=p.storepkid ";
WHERE 
    (p.purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59') OR
    (c.purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59')
GROUP BY 
    p.storepkid
ORDER BY  
    product_weekly_total DESC LIMIT 0,5

I want to get top 5 highest total records with sum of price and cprice fields. But above query is giving me wrong amounts in result.

If I remove 't_coupon_purchase' table from above query and fire query using just 't_product_purchase' then it is giving me correct result with correct amount.

Please let me know what wrong I am doing in my query or should I write it in other way for that I am not aware. Please provide some help on this.

---- EDIT - 01JAN2015 ---- SQL fiddle for my query http://www.sqlfiddle.com/#!2/0d4523/8

Here I have mentioned total of each storeid so it will helpful to compare with query result.

5 = 67 + 68 + 115 = 250

1 = 57 + 50 + 75 + 66 = 248

3 = 70 + 144 = 214

2 = 23 + 100 + 22 + 27 = 172

4 = 15 + 7 + 105 = 127

9 = 34

7 = 33

Answer 1

Join only what you want to join. Here you want to join product purchases of the week with coupon purchases of the week. But what you do join is any product purchase with any coupon purchase (where at least one of the couple must be purchased in the given week). So instead aggregate your product purchases and your coupon purchases and then join.

select
  coalesce(p.storeid,c.storeid) as storeid,
  coalesce(p.price,0) + coalesce(c.price,0) as product_weekly_total
from
(
  select storeid, sum(price) as price
  from t_product_purchase
  where purchasedate between '2014-12-27' and '2015-01-03'
  group by storeid
) p
full outer join
(
  select storeid, sum(cprice) as price
  from t_coupon_purchase
  where cpurchasedate between '2014-12-27' and '2015-01-03'
  group by storeid
) c on c.storeid = p.storeid
order by product_weekly_total desc 
limit 5;

I am using a full outer join here to also get stores having purchases in the week in only in one of the tables.

EDIT: I made two mistakes. One thing is I did p.price + c.price, but the summands can be NULL, so it must be coalesce(p.price,0) + coalesce(c.price,0) instead. I've changed that in the query above. The other thing is that MySQL doesn't support full outer joins. I don't change this in above query, so as to keep the query readable. Here is how to change it to mimic a full outer join in MySQL: A full outer join B is A left outer join B union A right outer join B. So:

select storepkid, product_weekly_total
from
(
  select
    coalesce(p.storepkid,c.storepkid) as storepkid,
    coalesce(p.price,0) + coalesce(c.price,0) as product_weekly_total
  from
  (
    select storepkid, sum(price) as price
    from t_product_purchase
    where purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59'
    group by storepkid
  ) p 
  left outer join
  (
    select storepkid, sum(price) as price
    from t_coupon_purchase
    where purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59'
    group by storepkid
  ) c on c.storepkid = p.storepkid
  union
  select
    coalesce(p.storepkid,c.storepkid) as storepkid,
    coalesce(p.price,0) + coalesce(c.price,0) as product_weekly_total
  from
  (
    select storepkid, sum(price) as price
    from t_product_purchase
    where purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59'
    group by storepkid
  ) p 
  right outer join
  (
    select storepkid, sum(price) as price
    from t_coupon_purchase
    where purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59'
    group by storepkid
  ) c on c.storepkid = p.storepkid
) all_results
order by product_weekly_total desc 
limit 5;

The fiddle: http://www.sqlfiddle.com/#!2/f269b/54.

Answer 2

Below query works for me.

SELECT t.storepkid, SUM(t.price) AS product_weekly_total
FROM 
(
    SELECT storepkid AS storepkid, price AS price 
    FROM t_product_purchase 
    WHERE purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59' 
    UNION ALL 
    SELECT storepkid, price FROM t_coupon_purchase
    WHERE purchasedatetime BETWEEN '2014-12-27 00:00:01' AND '2015-01-02 23:59:59' 
) t
GROUP BY storepkid
ORDER BY product_weekly_total DESC LIMIT 0,5 

SQL fiddle is available at this link : http://www.sqlfiddle.com/#!2/f269b/46

Answer 3

First, I think there is a typo on your 5th line t_coupon_purchase c on c.storeid=p.storeid ";. The "; should not be there.

Also, your constraints in the dates should have only one OR. The code is not executed because after your second OR, there is no other constraint.

Since you are LEFT JOINing you might be adding a number with a non-matching NULL coming from the LEFT JOINed table. Also, I don't think you can order the results as you do it in your query. Can you try the following and see what you get?

select storeid, product_weekly_total from 
(
SELECT 
    p.storeid, SUM(coalesce(p.price,0) + coalesce(c.cprice,0)) AS product_weekly_total 
FROM t_product_purchase p
LEFT JOIN 
    t_coupon_purchase c on c.storeid=p.storeid
WHERE 
    (p.purchasedate BETWEEN '2014-12-27' AND '2015-01-03') OR
    (c.cpurchasedate BETWEEN '2014-12-27' AND '2015-01-03')
GROUP BY 
    p.storeid
) as X
ORDER BY  
    product_weekly_total DESC LIMIT 0,5

So you are actually ordering the end result of the sum.

Thanks

EDIT From your datamodel seen in your fiddle, the lines:

(11, 7, 33.00, '2015-12-01 01:01:01'),
(12, 1, 75.00, '2015-12-01 07:07:57'),
(13, 5, 68.00, '2015-12-01 22:22:00'),
(14, 9, 34.00, '2015-12-01 09:06:00'),
(15, 2, 27.00, '2015-12-01 17:06:00');

Would not be returned as the date is december 1st 2015.