Jump to content

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


michelle1404

Recommended Posts

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

 

post-169154-0-79753200-1505408676_thumb.png

 

But for expense, i have to get the data from 2 tables, 'payments' and 'expense' , like this

 

post-169154-0-66889500-1505408748_thumb.png

post-169154-0-65297500-1505408775_thumb.png

 

My display should be like this

 

post-169154-0-98154200-1505408970_thumb.png

 

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.