MySQL Result from the same table

[ClickBuddy]

I have a table with the following relevant fields:

 

ID, BRN, CRED, AMT, USER

 

A basic explanation would be:

BRN = Branch

CRED = Creditor

AMT = Amount

USER = Debtor

 

What I want to do is get a result where I get the total amount (AMT) owed to each Creditor, which is easy, but I want the result to include the total owed by each branch. Now it has been a while since I have worked with JOINS and I only have a limited knowledge of working with SQL. so any help is appreciated.

 

SELECT SUM(t1.AMT) AS TOTAL, t1.CRED
FROM `201105` t1 INNER JOIN 
	(
		SELECT SUM(t1.AMT),t1.BRN
		FROM `201105` t1
		GROUP BY t1.BRN
	) t2 ON t1.CRED = t2.CRED
GROUP BY t1.CRED

Which gives me errors

[DaveyK]

What are the errors?

 

Also, you can simply consider using two selects inside your FROM

[Barand]

SELECT  t1.CRED, t1.BRN, SUM(t1.AMT) AS TOTAL,

FROM `201105` t1 

GROUP BY t1.CRED, t1.BRN

WITH ROLLUP

 

Edited March 18, 2013 by Barand

[ClickBuddy]

 

SELECT  t1.CRED, t1.BRN, SUM(t1.AMT) AS TOTAL,
FROM `201105` t1 
GROUP BY t1.CRED, t1.BRN
WITH ROLLUP

That works, thanks for the help, never seen WITH ROLLUP or used it.

 

Thanks again, Learn something new everyday