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
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