asked on

SubQuery link

Ive built the following query using MariaDB to look at a set of accounts and display the totals in columns which works well for one account:-

SELECT DISTINCT accounts.name,
	(SELECT SUM(amount) FROM transactions WHERE `datetime` <= NOW()) AS 'Current Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2016) AS '2016 Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2015) AS '2015 Totals'
FROM transactions
LEFT JOIN accounts ON transactions.`accNo` = accounts.`accountID`

Open in new window

The problem is I have multiple accounts (shown on the rows), what I need to do is somehow link the subquery to the parent query through accountID, but dont want to show accountID in the query (as I want the name of the account, and not the number I use for accountID).

Any ideas?

tonelm54

ASKER

Sorry, forgot to mention, the following query works:-

SELECT DISTINCT accounts.`accountID` AS 'parentID', accounts.name,
	(SELECT SUM(amount) FROM transactions WHERE `datetime` <= NOW() AND `accNo` = `parentID`) AS 'Current Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2016 AND `accNo` = `parentID`) AS '2016 Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2015 AND `accNo` = `parentID`) AS '2015 Totals'
FROM transactions
LEFT JOIN accounts ON transactions.`accNo` = accounts.`accountID`

Open in new window

But I dont really want to show 'parentID' if I can help it.

Mike Eghtebas

Select `accountID`, name, 'Current Totals'
From(
SELECT DISTINCT accounts.`accountID` AS 'parentID', accounts.name,
	(SELECT SUM(amount) FROM transactions WHERE `datetime` <= NOW() AND `accNo` = `parentID`) AS 'Current Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2016 AND `accNo` = `parentID`) AS '2016 Totals',
	(SELECT SUM(amount) FROM transactions WHERE YEAR(`datetime`) = 2015 AND `accNo` = `parentID`) AS '2015 Totals'
FROM transactions
LEFT JOIN accounts ON transactions.`accNo` = accounts.`accountID`) D

Open in new window

If you don't want to display accountID, then remove it from the first Select.

Mike

James0628

I haven't used MySQL, but it seems like you could just leave (accounts.`accountID` AS 'parentID') out of the main Select, and replace

`accNo` = `parentID`

in the sub-queries with

`accNo` = accounts.`accountID`

But if a column that's referenced in a sub-query has to be included in the main query, then you may have to wrap it all in another query, as Mike did.

And this is assuming that accounts.name is unique for every account. If some accounts could have the same name, then you may have to include accountID, or some other unique identifier, just to keep them separate. Also, your Left Join with accounts implies that there could be some transactions.accNo values that don't have a matching accounts.accountID, in which case you won't have a name (just null).

Having said all of that, using sub-queries to get the totals just doesn't seem very efficient. I think something like the following will work, and may be much more efficient.

SELECT accounts.name,
SUM (
  CASE
    WHEN `datetime` <= NOW() THEN amount
    ELSE 0
  END) AS 'Current Totals',
SUM (
  CASE
    WHEN YEAR(`datetime`) = 2016 THEN amount
    ELSE 0
  END) AS '2016 Totals',
SUM (
  CASE
    WHEN YEAR(`datetime`) = 2015 THEN amount
    ELSE 0
  END) AS '2015 Totals'
FROM transactions
LEFT JOIN accounts ON transactions.`accNo` = accounts.`accountID`
GROUP BY accounts.name

Open in new window

Like I said, I haven't used MySQL, so I may be missing something, but it seems pretty straightforward.

One other observation:

Current Totals appears to be everything up to today. Unless transactions includes items dated in the future, there's no need to check the date there. Every date will be today or earlier.

James

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial