How to calculate Monthly Sum of multiple table in single loop, using php and mysql?

Question

I have two table Table A and Table B in mysql Database

Table A

id     date     advanced         payed_remaining        remaining_date
1    1/1/2018     400                 800                   4/1/2018
2    2/1/2018     600                 600                   3/1/2018
3    4/1/2018     800                 200                   6/1/2018
4    6/1/2018     400                 300                   8/1/2018
5    3/2/2018     600                 200                   6/2/2018
6    8/2/2018     800                 400                   10/2/2018

Table B

id     date      amount    
1    1/1/2018     900     
2    2/1/2018     600     
3    4/1/2018     300 
4    2/2/2018     400
5    5/2/2018     800

Query for fetching monthly Data from both table,

$monthly_res = $con->prepare("SELECT t2.month, t2.total_advance, t2.total_pay_remaining, t1.total_income_amount FROM (SELECT  month(B.date) as month, SUM(B.income_amount) as total_income_amount FROM B group by month(B.date)) t1 INNER JOIN (SELECT month(A.date) as month, month(A.due_date) as month, t1.total_income_amount, SUM(A.advance) as total_advance, SUM(A.pay_remaining) as total_pay_remaining, t1.total_income_amount FROM A) t2 ON t2.month  = t1.month");
    $monthly_res->execute();
    while ($row = $monthly_res->fetch(PDO::FETCH_ASSOC)) {
        $month = $row['month'];
        $dt = DateTime::createFromFormat('!m', $month);
        $month_name = $dt->format('F');
        $total = $row['total_advance'] + $row['total_income_amount'] + $row['total_pay'];
        echo "<tbody>
            <tr>
                <td>".$month_name."</td>
                <td>".$total."/-</td>
            </tr>
        </tbody>";
    }

1054 - Unknown column 't1.total_income_amount' in 'field list'

when i remove t1.total_income_amount from query its showing December result only...

I want fetch monthly sum of advanced, sum of payed_remaining, sum of amount in one loop.

Result= sum(advanced) + sum(payed_remaining) + sum(amount) by month 

January : 5900

February : 3200

Siva · Accepted Answer · 2019-01-19 10:47:51Z

1

Can you try with below query

SELECT month(A.date) as month, SUM(A.advanced) as total_advance, SUM(A.payed_remaining) as total_pay_remaining, month(B.date) as month, SUM(B.income_amount) as total_income_amount 
From A
join B on month(A.date) = month(B.date)
group by month(A.date)

answered Jan 19, 2019 at 10:47

Siva

1,5271 gold badge19 silver badges29 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2019-01-22 09:05:07Z

1

You should join the aggregated result eg:

      SELECT t2.month
      , t2.total_advance
      , t2.total_pay_remaining
      , t1.total_income_amount FROM (
            SELECT  month(B.date) as month
              , SUM(B.income_amount) as total_income_amount 
            FROM B group by month(B.date)
      ) t1 
      INNER JOIN (  
            SELECT month(A.date) as month
              , SUM(A.advance) as total_advance
              , SUM(A.pay_remaining) as total_pay_remaining
            FROM A
      ) t2 ON t2.month  = t1.month

You should explict join clause and AND operator and join the sum of the b table

edited Jan 22, 2019 at 9:05

answered Jan 19, 2019 at 10:50

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

8 Comments

Permendra Singh Over a year ago

SELECT t2.month, t2.total_advance, t2.total_pay_remaining, t1.total_income_amount FROM ( SELECT month(B.date) as month SUM(B.income_amount) as total_income_amount FROM B group by month(B.date)) t1 INNER JOIN ( SELECT month(A.date) as month, month(A.remaining_date) as month, SUM(A.advanced) as total_advance, SUM(A.pay_remaining) as total_pay_remaining, t1.total_income_amount FROM A) t2 ON t2.month = t1.month

Permendra Singh Over a year ago

I try with above query but its showing syntax error

ScaisEdge Over a year ago

answer update .. eventually show me the exact error message

Permendra Singh Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(B.income_amount) as total_income_amount FROM income group by mon' at line 7

Permendra Singh Over a year ago

#1054 - Unknown column 't1.total_income_amount' in 'field list'

|

Collectives™ on Stack Overflow

How to calculate Monthly Sum of multiple table in single loop, using php and mysql?

1054 - Unknown column 't1.total_income_amount' in 'field list'

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1054 - Unknown column 't1.total_income_amount' in 'field list'

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related