getting data from multiple tables with sum in a single result set

[michelle1404]

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?

[requinix]

How about just doing another UNION ALL?

[michelle1404]

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)";

[requinix]

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...").

[michelle1404]

@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

 

 

 

[requinix]

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

[michelle1404]

@requinix

 

Thank you very much.