MySQL multiple sum table

Question

from the following fiddle http://sqlfiddle.com/#!2/8f1d2/5 I want to sum crossing table as the query below, but i got trouble with duplicate value of sale_event. do you have any idea about it.

SELECT
 a.province_id,
 SUM(a.sale_event) AS sale_eventTotal,
 SUM(d.order_fullpay) AS order_fullpayTotal
FROM
    san_activites a
LEFT JOIN
    sale_event_detail d
ON
    a.san_activity_id = d.san_activity_id
WHERE 
    a.entry_date BETWEEN '2014-7-1' AND '2014-7-30'
GROUP BY a.province_id

hm... if you look detail at the insert row... I would expected on province_id 2 should have sale_event only 4 not 8 which it is a duplicate value — user3736346, Jul 23 '14 at 02:26
You seem to have duplicate rows in your data. Are you saying you want to ignore duplicates? — shree.pat18, Jul 23 '14 at 02:32

score 0 · Answer 1 · answered Jul 23 '14 at 02:15

0

Just Remove GROUP BY a.province_id and run the query it worked for me.

answered Jul 23 '14 at 02:15

Mohit S

13,723
6
34
69

Hm... I need that group by province friend. – user3736346 Jul 23 '14 at 02:23

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

Either add a unique index on san_activity_id, or dedupe at query time:

SELECT 
 SUM(a.sale_event) AS sale_eventTotal,
 SUM(d.order_fullpay) AS order_fullpayTotal
FROM
    san_activites a
LEFT JOIN
    (select * from sale_event_detail group by san_activity_id) d
ON
    a.san_activity_id = d.san_activity_id
WHERE 
    a.entry_date BETWEEN '2014-7-1' AND '2014-7-30'
GROUP BY a.province_id

fiddle

edited May 23 '17 at 10:33

Community

1
1

answered Jul 23 '14 at 02:32

Fabricator

12,722
2
27
40

Hi friend really thanks for your help. You almost right but just on the order_fullpay I would expected for 20. Thank you very much. – user3736346 Jul 23 '14 at 04:19

score 0 · Accepted Answer · answered Jul 23 '14 at 04:12

I was able to get the results I think you're wanting below. the result brings back
3 | 10
4 | 20

SELECT 
 SUM(a.sale_event) AS sale_eventTotal,
 SUM(d.order_fullpayTotal) AS order_fullpayTotal
FROM
    san_activites a
LEFT JOIN
    (select SUM(order_fullpay) AS order_fullpayTotal, san_activity_id from sale_event_detail group by san_activity_id) d
ON
    a.san_activity_id = d.san_activity_id
WHERE 
    a.entry_date BETWEEN '2014-7-1' AND '2014-7-30'
GROUP BY a.province_id

Fiddle

MySQL multiple sum table

3 Answers3