MySQL Join Two Tables and Sum from both Table

Question

I have two table & Need sum of column from both table, i try but cant get result

table-1 Delivery:

Table- Collection:

My inner join query was:

SELECT loan_collection.coll_date,
       sum(loan_collection.coloan_amo) AS coloan_amo,
       sum(loan_collection.sc_coll) AS sc_coll,
       sum(loan_collection.total_coll) AS total_coll,
       loan_collection.year,
       bag_del_rentcoll.rent_bagdel,
       bag_del_rentcoll.rent_amo,
       bag_del_rentcoll.booking,
       bag_del_rentcoll.rent_rece
FROM loan_collection AS loan_collection
INNER JOIN
  (SELECT del_date,
          sum(rent_bagdel) AS rent_bagdel,
          sum(rent_amo) AS rent_amo,
          sum(booking) AS booking,
          sum(rent_rece) AS rent_rece
   FROM bag_del_rentcoll) AS bag_del_rentcoll
GROUP BY loan_collection.coll_date

The result is: as you can see it's sum of all from table two. How can i get result as

Thanks.

That GROUP BY is invalid, will return an unpredictable result in older MySQL versions, will raise an error in new versions (unless in compatibility mode.) The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh
– jarlh, Commented Feb 8, 2017 at 10:43

Thorsten Kettner · Accepted Answer · 2017-02-08 11:13:09Z

1

Aggregate first, then join. I suppose you want to join on del_date = coll_date. Here is the complete query:

SELECT 
  lc.coll_date,
  lc.sum_coloan_amo,
  lc.sum_sc_coll,
  lc.sum_total_coll,
  lc.max_year,
  bdr.sum_rent_bagdel,
  bdr.sum_rent_amo,
  bdr.sum_booking,
  bdr.sum_rent_rece 
FROM 
(
  SELECT
    coll_date,
    SUM(coloan_amo) AS sum_coloan_amo
    SUM(sc_coll) AS sum_sc_coll,
    SUM(total_coll) AS sum_total_coll,
    MAX(year) AS max_year
  FROM loan_collection
  GROUP BY coll_date
) lc
INNER JOIN
(
  SELECT 
    del_date,
    sum(rent_bagdel) AS sum_rent_bagdel,
    sum(rent_amo) AS sum_rent_amo,
    sum(booking) AS sum_booking,
    sum(rent_rece) AS sum_rent_rece
  FROM bag_del_rentcoll
  GROUP BY del_date
) bdr ON bdr.del_date = lc.coll_date;

answered Feb 8, 2017 at 11:13

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join Two Tables and Sum from both Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related