db2 luw - Summing and counting across multiple tables in SQL -

db2 luw - Summing and counting across multiple tables in SQL -

i have 3 tables need select , summarize data.

table: thought reference sl 128 sl1 200 sl1 201 sl2 205 sl3 table: acct1 idea_ref accts 128 5 128 2 200 3 205 4 table: acct2 idea_ref accts 201 3 205 4 205 3

what need pull summary sorted sl totals accts field of both tables.

here sql using far:

select i.sl sl, count(distinct i.reference) no, sum(case when a1.idea_ref=i.reference a1.accts else 0 end) acct1, sum(case when a2.idea_ref=i.reference a2.accts else 0 end) acct2 thought left bring together acct1 a1 on a1.idea_ref=i.reference left bring together acct2 a2 on a2.idea_ref=i.reference a2.idea_ref in i.reference or a1.idea_ref in i.reference grouping i.sl

the problem finding when there multiple values in acct1 , acct2 tables reference thought table. here results query:

sl no acct1 acct2 sl1 2 10 0 sl2 1 0 3 sl3 1 8 7

the sl3 line adds acct1 , acct2 values 2 times. can't seem find right way add together them appropriate number of times.

the desired output is:

sl no acct1 acct2 sl1 2 10 0 sl2 1 0 3 sl3 1 4 7

any help much appreciated.

you hare asking 3 separate aggregates, you're trying compute them in single query.

to no (count of distinct items) can do

select sl, count(*) no thought grouping sl

to acct1 item can do:

select sl, sum(accts) acct1 thought bring together acct1 on idea.reference = acct1.idea_ref grouping sl

in manner can acct2

select sl, sum(accts) acct2 thought bring together acct2 on idea.reference = acct2.idea_ref grouping sl

then, need bring together these aggregate queries on sl result set. because have missing entries in of aggregates, need left in left join , coalesce() items.

sql fiddle

here's on query

select q.sl, no, coalesce(acct1,0) acct1, coalesce(acct2,0) acct2 ( select sl, count(*) no thought grouping sl ) q left bring together ( select sl, sum(accts) acct1 thought bring together acct1 on idea.reference = acct1.idea_ref grouping sl ) r on q.sl = r.sl left bring together ( select sl, sum(accts) acct2 thought bring together acct2 on idea.reference = acct2.idea_ref grouping sl ) s on q.sl = s.sl

the result looking for:

| sl | no | acct1 | acct2 | |-----|----|-------|-------| | sl1 | 2 | 10 | 0 | | sl2 | 1 | 0 | 3 | | sl3 | 1 | 4 | 7 |

see how works? have each aggregate separately.

if you're using dbms doesn't know join ... using() syntax, set in on q.sl = r.sl or appropriate on clause instead. see edit, , see fiddle: http://sqlfiddle.com/#!2/63aa1/3/0

sql db2-luw