michelle1404 Posted September 14, 2017 Share Posted September 14, 2017 Hi, I am trying to get the income-expense report on month basis. Actually for income, i am getting data from 'receipt' table structure is like this But for expense, i have to get the data from 2 tables, 'payments' and 'expense' , like this My display should be like this Actually for only 2 tables i got the result in the same format. Here is the working query $sqlm = "SELECT r.invoice_id, r.customer, r.paid_amount, r.mode, r.ref_no, MONTHNAME(r.paid_date) AS 'Month', YEAR(r.paid_date) AS Yr, r.comments, l.company, 'Income' AS 'Income' FROM receipt r INNER JOIN leads l ON r.customer=l.id GROUP BY MONTH(r.paid_date) UNION ALL SELECT p.invoice_id, p.vendor, p.paid_amount, p.mode, p.ref_no, MONTHNAME(p.paid_date) AS 'Month', YEAR(p.paid_date) AS Yr, p.comments, v.name, 'Expense' AS 'Income' FROM payments p INNER JOIN vendor v ON p.vendor=v.id GROUP BY MONTH(p.paid_date) ORDER BY Yr DESC "; $query = mysqli_query($con, $sqlm) ?> <table> <tr><th>Paid Amount</th> <th>Details</th> <th>Month</th> </tr> <?php while($row = mysqli_fetch_array($query)) { ?> <tr> <td><?php echo $row['paid_amount']; ?></td> <td><?php echo $row['Income']; ?></td> <td><?php echo $row['Month']; ?></td> </tr> <?php } ?> </table> But only for expense i should add 2 tables data, which i am not getting. Can somebody guide me on this? Link to comment Share on other sites More sharing options...
requinix Posted September 14, 2017 Share Posted September 14, 2017 How about just doing another UNION ALL? Link to comment Share on other sites More sharing options...
michelle1404 Posted September 15, 2017 Author Share Posted September 15, 2017 i tried doing it like this, but not getting it, income is only from 1 table but expense should fetch add sum 2 tables data. $sqlm = "(SELECT r.paid_amount, MONTHNAME(r.paid_date) AS 'Month', YEAR(r.paid_date) AS Yr, 'Income' AS 'Income' FROM receipt r INNER JOIN leads l ON r.customer=l.id GROUP BY MONTH(r.paid_date)) UNION ALL (SELECT SUM(pamt) AS paid_amount, 'Income' AS 'Expense' FROM ( Select sum(paid_amount) as pamt FROM expense_pay WHERE MONTHNAME(paid_date) AS 'Month', YEAR(paid_date) AS Yr UNION ALL Select sum(paid_amount) as pamt FROM payments WHERE MONTHNAME(paid_date) AS 'Month', YEAR(paid_date) AS Yr ) GROUP BY MONTH(paid_date) ORDER BY Yr DESC)"; Link to comment Share on other sites More sharing options...
requinix Posted September 15, 2017 Share Posted September 15, 2017 It doesn't need to be that complicated. Take your first query, tack on another "UNION ALL", then add a query like you did for payments but for expenses. "SELECT r.invoice_id, r.customer, r.paid_amount, r.mode, r.ref_no, MONTHNAME(r.paid_date) AS 'Month', YEAR(r.paid_date) AS Yr, r.comments, l.company, 'Income' AS 'Income' FROM receipt r INNER JOIN leads l ON r.customer=l.id GROUP BY MONTH(r.paid_date) UNION ALL SELECT p.invoice_id, p.vendor, p.paid_amount, p.mode, p.ref_no, MONTHNAME(p.paid_date) AS 'Month', YEAR(p.paid_date) AS Yr, p.comments, v.name, 'Expense' AS 'Income' FROM payments p INNER JOIN vendor v ON p.vendor=v.id GROUP BY MONTH(p.paid_date) ORDER BY Yr DESC UNION ALL SELECT e.eid, e.user, e.paid_amount, e.mode, e.ref_no, MONTHNAME(e.paid_date) AS 'Month', YEAR(e.paid_date) AS Yr, e.comments, ???, 'Expense' AS 'Income' FROM expense e INNER JOIN ??? GROUP BY MONTH(e.paid_date) ORDER BY Yr DESC ";Looking at the SQL I have a couple more comments:- Grouping by just the MONTH alone isn't safe: it'll group January 2016 and January 2017 together. If you constrain the query to a single year then it's okay, but otherwise you should group by both the year and month. - The column names are determined by the first SELECT query. You don't actually need the column aliases for the other two queries. Not that they're bad. - Consider turning that query into a view... If you do, omit the grouping and ordering and instead put those into your query against the view itself ("SELECT * FROM name_of_view ORDER BY... GROUP BY..."). Link to comment Share on other sites More sharing options...
michelle1404 Posted September 15, 2017 Author Share Posted September 15, 2017 @requinix Thanks. In this i get the result, but i want each months data to be added and show it as expense in that month... Now am getting like this But i want to display like this Link to comment Share on other sites More sharing options...
requinix Posted September 15, 2017 Share Posted September 15, 2017 Well now you're changing the requirements, but okay. First we have to fix your query to be better and more suitable for a view. A view makes it much easier to use this query, and in other places. SELECT r.invoice_id, r.customer, r.paid_amount, r.mode, r.ref_no, r.paid_date, r.comments, l.company, 'Income' AS IncomeOrExpense FROM receipt r INNER JOIN leads l ON r.customer=l.id UNION ALL SELECT p.invoice_id, p.vendor, p.paid_amount, p.mode, p.ref_no, p.paid_date, p.comments, v.name, 'Expense' FROM payments p INNER JOIN vendor v ON p.vendor=v.id UNION ALL SELECT e.eid, e.user, e.paid_amount, e.mode, e.ref_no, e.paid_date, e.comments, ???, 'Expense' FROM expense e INNER JOIN ??? Then put it into the view. CREATE VIEW pick_a_name AS SELECT...Now make a new query to select from the view and do the grouping and sorting. SELECT YEAR(v.paid_date) AS Yr, MONTHNAME(v.paid_date) AS Month, SUM(IF(v.IncomeOrExpense = 'Income', v.paid_amount, 0)) AS Income, SUM(IF(v.IncomeOrExpense = 'Expense', v.paid_amount, 0)) AS Expense FROM pick_a_name v GROUP BY Yr, Month ORDER BY Yr DESC, Month DESC Link to comment Share on other sites More sharing options...
michelle1404 Posted September 15, 2017 Author Share Posted September 15, 2017 @requinix Thank you very much. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.