Avatar of neptune2528
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
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
neptune2528
Avatar of JMattias
JMattias
Flag of Sweden image

Hi,

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
Avatar of JMattias
JMattias
Flag of Sweden image

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'
Avatar of nigelrivett
nigelrivett

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
ASKER CERTIFIED SOLUTION
Avatar of nigelrivett
nigelrivett

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of neptune2528
neptune2528

ASKER

nigelrivett,

I tried your last suggestion, but got an error reading there was a syntax error in the FROM clause.  Any ideas?
Avatar of nigelrivett
nigelrivett

looks ok can you post what you ran.
Do you know which from clause?
Avatar of nigelrivett
nigelrivett

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
Avatar of neptune2528
neptune2528

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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo