October 29, 2010 at 11:04 am
Hi SQL people.
I have a problem creating the correct SQL syntax to solve a problem, maybe someone out there has a helpful hint.
I have two tables, one containing financial posts ("daybook") and one containing all the accounts ("accounts").
I'm looking for a result set that contains all accounts and all (if any) corresponding totals from the daybook.
Pseudo example below:
Table: daybook
accountnumberinoutisactive
--------------------------------------------------------
10001000True
1003500True
100325-5True
1005600-50False
Table: accounts
accountnumbername
----------------------------
1000Income
1001Expense
1002Sales
1003Marketing
1004Payment
1005Bank
Desired result:
accountnumberamount
----------------------------
1000100
1001NULL
1002NULL
100370
1004NULL
1005NULL
nth attempt:
SELECT d.accountnumber, SUM(in+out) AS amount, a.accountnumber
FROM daybook as d
JOIN accounts AS a ON d.accountnumber = a.accountnumber
WHERE
d.isactive='true'
GROUP BY d.accountnumber, a.accountnumber
The syntax above of course only displays matching records (1000,1003,1005), but how can I fill in the blanks?
I have worked with OUTER, INTER, LEFT JOINs etc. and multiple queries via UNION, EXCEPT or merging datasets in the non-SQL code,
but none if it produces the desired result.
Any and all hints much appreciated!
Kind regards,
Waterfield.
October 29, 2010 at 11:09 am
select accountnumber,
(select sum(in + out)
from daybook
where accountnumber = accounts.accountnumber)
from accounts;
You can wrap the inline query in a Coalesce/IsNull function if you prefer 0 over Null in that column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 11:49 am
That worked beautifully! Thank you kindly for the elegant solution.
November 2, 2010 at 8:13 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply