neptune2528
asked on
SQL SUM Across Tables
OK, I have to write a query to sum the values returned from multiple tables. The details are as follows:
TABLE A contains the field I would like to sum, lets call it NUMBER in TABLE A. TABLE B contains a set of classifications for records in TABLE A. The field in TABLE B is CLASSIFICATION and can have the following values: CLASS 1, CLASS 2 and CLASS 3. A single record in TABLE A can, and will, have many classifications in TABLE B. For example, a single record in TABLE A can be CLASS 1, CLASS 2 and CLASS 3 and any combination thereof.
I want to be able to sum the NUMBER field from TABLE A across records that have related records in TABLE B for CLASS 1, CLASS 2 and CLASS 3. Please note in the example, the record in TABLE A must have all three classes to be valid for the summation. If it only has CLASS 1 and CLASS 2, then it should not be included.
Assume a simplified data model as follows:
TABLE A
- ID (unique identifier)
- NUMBER
TABLE B
- ID (relationship to TABLE A)
- Classification
TABLE A contains the field I would like to sum, lets call it NUMBER in TABLE A. TABLE B contains a set of classifications for records in TABLE A. The field in TABLE B is CLASSIFICATION and can have the following values: CLASS 1, CLASS 2 and CLASS 3. A single record in TABLE A can, and will, have many classifications in TABLE B. For example, a single record in TABLE A can be CLASS 1, CLASS 2 and CLASS 3 and any combination thereof.
I want to be able to sum the NUMBER field from TABLE A across records that have related records in TABLE B for CLASS 1, CLASS 2 and CLASS 3. Please note in the example, the record in TABLE A must have all three classes to be valid for the summation. If it only has CLASS 1 and CLASS 2, then it should not be included.
Assume a simplified data model as follows:
TABLE A
- ID (unique identifier)
- NUMBER
TABLE B
- ID (relationship to TABLE A)
- Classification
Sorry, typo on last join:
select
sum(mysum.number)
from
(
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS1'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS2'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS3'
) as mysum
inner join tableb bb
on bb.classification = 'CLASS1'
inner join tableb bbb
on bbb.classification = 'CLASS2'
inner join tableb bbbb
on bbbb.classification = 'CLASS3'
select
sum(mysum.number)
from
(
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS1'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS2'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS3'
) as mysum
inner join tableb bb
on bb.classification = 'CLASS1'
inner join tableb bbb
on bbb.classification = 'CLASS2'
inner join tableb bbbb
on bbbb.classification = 'CLASS3'
select result = sum(a.number)
from tableA a
join
(select ID from TableB where classification in ('CLASS 1','CLASS 2','CLASS 3') group by classification having count(distinct classification) = 3) b
on a.id = b.id
from tableA a
join
(select ID from TableB where classification in ('CLASS 1','CLASS 2','CLASS 3') group by classification having count(distinct classification) = 3) b
on a.id = b.id
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nigelrivett,
I tried your last suggestion, but got an error reading there was a syntax error in the FROM clause. Any ideas?
I tried your last suggestion, but got an error reading there was a syntax error in the FROM clause. Any ideas?
looks ok can you post what you ran.
Do you know which from clause?
Do you know which from clause?
whoa - I seem to have drifted into the access forum
try
select result = sum(a.number)
from tableA a
join
(select ID from TableB where classification in ('CLASS 1','CLASS 2','CLASS 3') group by id having count(*) = 3) b
on a.id = b.id
try
select result = sum(a.number)
from tableA a
join
(select ID from TableB where classification in ('CLASS 1','CLASS 2','CLASS 3') group by id having count(*) = 3) b
on a.id = b.id
ASKER
nigelrivett,
I made a few changes to your suggestion and it worked well. As such, I will be accepting you previous selection. Thanks for your help.
I made a few changes to your suggestion and it worked well. As such, I will be accepting you previous selection. Thanks for your help.
If you mean that they will and must have all of the classifications and the classification CLASS1, CLASS2 and CLASS3 is the olnu ones the following should work.
select
sum(mysum.number)
from
(
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS1'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS2'
UNION
select id, number from tablea a inner join tableb b on a.id = b.id and a.classification = 'CLASS3'
) as mysum
inner join tableb bb
on bb.classification = 'CLASS1'
inner join tableb bbb
on bbb.classification = 'CLASS2'
inner join tableb bbbb
on bb.classification = 'CLASS3'
But I'm not sure if it's allowed to have a UNION in a derived table?
Regards
/Mattias