CODEWITHSUNDEEP
mysqljoinleft-join
Ria W
Ria W

Reputation: 67

JOIN 3 tables SUM group by month

I'm trying to join 3 table group by month and calculating sum from 2 tables. I can join all the tables, but the sums are not right. My tables:

Table month

Table invoice

Table purchase

My query:

$query = $this->db->query("SELECT month.month_name as month, 
    SUM(table_purchase.subtotal) AS total_pur,
    SUM(table_invoice.subtotal) AS total_inv  
    FROM month 
    LEFT JOIN table_purchase ON (month.month_num = MONTH(table_purchase.date_pur) AND YEAR(table_purchase.date)= '2018')
    LEFT JOIN table_invoice ON (month.month_num = MONTH(table_invoice.date_inv) AND YEAR(table_invoice.date)= '2018')
    GROUP BY month.month_name ORDER BY month.month_num");

The result should be like this:

+---------------------------+
|Month| Total Inv| Total Pur|
+---------------------------+
| Jan |    30    |  80      |
+---------------------------+
| Feb |    90    |  48      |
+---------------------------+
| Mar |   110    |  42      |
+---------------------------+
| Apr |    60    |  96      |
+---------------------------+
| May |  null    |  72      |
+---------------------------+
| Jun |    30    |  56      | 
+---------------------------+
| Jul |    32    |  10      |
+---------------------------+
| Agt |    48    |  36      |
+---------------------------+
| Sep |  null    | null     |
+---------------------------+
| Okt |  null    | null     |
+---------------------------+
| Nov |  null    | null     |
+---------------------------+
| Des |  null    | null     |
+---------------------------+

But I got the wrong sums for months. I think it repeats itself. I've tried the like inner join, and join. But I still got the wrong sums. How to make it right? Thanks

Edit, data from my table:

month:

INSERT INTO `month` (`month_num`, `month_name`) VALUES
(1, 'Jan'),
(2, 'Feb'),
(3, 'Mar'),
(4, 'Apr'),
(5, 'May'),
(6, 'Jun'),
(7, 'Jul'),
(8, 'Agt'),
(9, 'Sep'),
(10, 'Okt'),
(11, 'Nov'),
(12, 'Des');

table_invoice

INSERT INTO `table_invoice` (`id`, `ref`, `med`, `unit_price`, `qty`, `subtotal`, `customer`, `date`, `grandtotal`) VALUES
(80, 'JMBSErmmo0', 'Adona', 15000, 1, 15000, 'Melinda', '2018-08-09', 29000),
(81, 'JMBSErmmo0', 'Asam Mefenamat', 4000, 1, 4000, 'Melinda', '2018-08-09', 29000),
(82, 'JMBSErmmo0', 'Salbutamol', 10000, 1, 10000, 'Melinda', '2018-08-09', 29000),
(87, 'xl23Q6whsP', 'Salbutamol', 10000, 3, 30000, 'Melinda', '2018-01-10', 30000),
(90, '9ab7RcZHma', 'Adrome', 15000, 6, 90000, 'Doni', '2018-02-14', 90000),
(91, 'fAMEjr9rA0', 'Ambroxol', 22000, 5, 110000, 'Amirullah', '2018-03-14', 110000),
(92, 'so5518T1vI', 'Salbutamol', 10000, 6, 60000, 'Doni', '2018-04-18', 60000),
(95, 'M5mhiUJs5y', 'Adona', 15000, 1, 15000, 'Melinda', '2018-08-22', 19000),
(96, 'M5mhiUJs5y', 'Asam Mefenamat', 4000, 1, 4000, 'Melinda', '2018-08-22', 19000),
(97, 'VS27jjD5Ze', 'Ambroxol', 22000, 1, 22000, 'Pitaloka', '2018-07-18', 32000),
(98, 'VS27jjD5Ze', 'Stimuno', 10000, 1, 10000, 'Pitaloka', '2018-07-18', 32000),
(99, 'nyptFm9i43', 'Stimuno', 10000, 3, 30000, 'Andi', '2018-06-13', 30000),
(100, '3ilz1ZvPj5', 'Stimuno', 10000, 11, 110000, 'Andi', '2017-05-17', 110000);

table_purchase

INSERT INTO `table_purchase` (`id`, `ref`, `med`, `unit_price`, `qty`, `subtotal`, `supplier`, `date`, `grandtotal`) VALUES
(3, 'L8BvtCyFDZ', 'Salbutamol', 6000, 3, 18000, 'Bina Jaya Apotek', '2018-08-11', 24000),
(4, 'L8BvtCyFDZ', 'Stimuno', 6000, 1, 6000, 'Bina Jaya Apotek', '2018-08-11', 24000),
(8, 'pwoaFcmGVs', 'Asam Mefenamat', 4000, 12, 48000, 'Bina Jaya Apotek', '2018-02-22', 48000),
(9, '96kHYMepS9', 'Salbutamol', 6000, 7, 42000, 'Bina Jaya Apotek', '2018-03-21', 42000),
(10, 'Tbb6pLVCJr', 'Ambroxol', 20000, 4, 80000, 'Bina Jaya Apotek', '2018-01-24', 80000),
(11, 'fdMGrBa4nS', 'Stimuno', 6000, 16, 96000, 'Bina Jaya Apotek', '2018-04-18', 96000),
(12, 'aDxtUA0rsc', 'Adrome', 12000, 6, 72000, 'Bina Jaya Apotek', '2018-05-09', 72000),
(13, '5UCwWCC7Yb', 'Asam Mefenamat', 4000, 3, 12000, 'Bina Jaya Apotek', '2018-08-16', 12000),
(14, 'KCZxZ1MqID', 'Ambroxol', 20000, 2, 40000, 'Bina Jaya Apotek', '2018-07-18', 40000),
(15, 'Ar9235n1ny', 'Adrome', 12000, 3, 36000, 'Kenanga Apotek', '2018-06-06', 56000),
(16, 'Ar9235n1ny', 'Ambroxol', 20000, 1, 20000, 'Kenanga Apotek', '2018-06-06', 56000);

Upvotes: 1

Views: 838

Answers (2)

Nick
Nick

Reputation: 147166

Your problem is occurring because the JOINs are creating a multiplying effect; for example, when there are 3 invoices and 2 purchases in the same month you will get 6 rows of data (3*2), causing all the values to be too large. You need to sum all the purchases and invoices for each month in each year separately, and then JOIN the results to the month table:

SELECT m.month_name as month, 
    p.total_pur,
    i.total_inv  
    FROM month m
    LEFT JOIN (SELECT YEAR(date) AS y, MONTH(date) AS m, SUM(subtotal) AS total_pur FROM table_purchase GROUP BY y, m) p
        ON m.month_num = p.m AND p.y = 2018
    LEFT JOIN (SELECT YEAR(date) AS y, MONTH(date) AS m, SUM(subtotal) AS total_inv FROM table_invoice GROUP BY y, m) i
        ON m.month_num = i.m AND i.y = 2018
    GROUP BY m.month_name ORDER BY month

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Calculate sum for invoice and purchase in different sub clause and then do a left join with your month table

SELECT m.month_name as month, 
   i.total_inv, 
   p.total_pur
FROM month m
LEFT JOIN (SELECT MONTH(date_inv) as month, 
            SUM(subtotal) as total_inv  
            FROM table_invoice
            WHERE YEAR(date_inv)= '2018'
            GROUP BY month) i  ON (m.month_num = i.month)    
LEFT JOIN (SELECT MONTH(date_pur) as month, 
            SUM(subtotal) as total_pur
            FROM  table_purchase 
            WHERE YEAR(date_pur)= '2018'
            GROUP BY month) p ON (m.month_num = p.month )
ORDER BY m.month_num

Upvotes: 3

Related Questions