# Thread: Query to get data from 2 similar tables

1. Registered User
Join Date
Feb 2010
Posts
22

## Unanswered: Query to get data from 2 similar tables

Hi,

I have 2 different tables with same structure and same fields as mentioned below:

Table A:
Code:
Code	Cost
A	100
A	20
A	30
A	5
B	50
B	40
B	30
C	35
C	25
Table B:
Code:
Code	Cost
A	100
A	20
A	30
B	50
B	30
C	35
C	25
D	10
I need Count and Sum from both these tables. I result would be:

Code:
Code	Count	Sum
A	7	305
B	5	200
C	4	120
D	1	10
Data to be grouped on Code
Count = Count from Table A + Count from Table B
Sum = Sum of Cost from Table A + Sum of Cost from Table B

Please suggest a query for this.

2. Moderator
Join Date
Mar 2009
Posts
5,442
If you need to perform this kind of operation, it most probably means that your database is not normalized. Here's a solution but it's not nice!

a) If you simply try to perform a UNION with both tables and SUM and GROUP BY on the resulting data set:
Code:
SELECT U.Code, Sum(U.Cost) AS SumOfCost
FROM (SELECT Code, Cost
FROM Table_1
UNION
SELECT Code, Cost
FROM Table_2)
AS U
GROUP BY U.Code;
You would get the wrong (and quite surprising) result:

Code:
Code	SumOfCost
A	155
B	120
C	 60
D	 10

b) Trying to group and sum the rows of each table then GROUP BY and SUM the resulting data set would not be better (and quite surprising too):
Code:
SELECT U.Code, Sum(U.sCost) AS SumOfCost
FROM (SELECT Code, Sum(Cost) AS sCost
FROM Table_1
GROUP BY Code
UNION
SELECT Code, Sum(Cost) AS sCost
FROM Table_2
GROUP BY Code)
AS U
GROUP BY U.Code;
Code:
Code	SumOfCost
A	305
B	200
C	 60
D	 10
In both cases, some kind of implicit DISTINCT or DISTINCTROW predicate seems to be applied on the SUM operation. This is not specific to Access: the same queries performed in SQL Server yield the same results.

c) If we can find a way to disambiguate the values in the Cost column without changing the contents of the Code column and without altering the resulting sums, we could get the expected results, like this:
Code:
SELECT U.Code, CLng(Sum(U.sCost)) AS SumOfCost
FROM (SELECT Code, (Cost+0.0000001) AS sCost
FROM Table_1
UNION
SELECT Code, Cost
FROM Table_2 ) AS U
GROUP BY U.Code;
Which yields:
Code:
Code	SumOfCost
A	305
B	200
C	120
D	 10
The only condition for this method to produce the correct results being that the increment of the Cost column in Table_1 must be small enough as not to become significant when summed with the original value.

3. Registered User
Join Date
May 2005
Location
Posts
2,888
Sinndho, I don't think you need the "+0.0000001" bit. UNION by default eliminates duplicates, so switching that to UNION ALL should make the first query work. It will also be faster.
Last edited by pbaldy; 09-20-11 at 17:03. Reason: Fix spelling; sorry!

4. Moderator
Join Date
Mar 2009
Posts
5,442
You're right I did not think of that. Thanks!

5. Registered User
Join Date
May 2005
Location
Posts
2,888
Happy to help!

6. Registered User
Join Date
Feb 2010
Posts
22
Thanks a lot, Sinndho & pbaldy!

I got the result using UNION ALL along with Counts:
Code:
SELECT U.Code, Count(U.Cost) AS CountOFCost, Sum(U.Cost) AS SumOfCost
FROM (SELECT Code, Cost
FROM Table_1
UNION ALL
SELECT Code, Cost
FROM Table_2
)  AS U
GROUP BY U.Code;
However, I have another quick question:
How do I get the result if I code a specific condition. I tried this (for condition Cost > 20):

Code:
SELECT U.Code, Count(U.Cost) AS CountOFCost, Sum(U.Cost) AS SumOfCost
FROM (SELECT Code, Cost
FROM Tab
WHERE cost > 20
UNION ALL
SELECT Code, Cost
FROM Tab2
WHERE cost > 20
)  AS U
GROUP BY U.Code;
The result was:
Code:
Code	CountOFCost	SumOfCost
A	4	260
B	5	200
C	4	120
Is it possible to get result even for Code D with values 0?
Code:
Code	CountOFCost	SumOfCost
A	4	260
B	5	200
C	4	120
D	0	0
Thanks.

7. Moderator
Join Date
Mar 2009
Posts
5,442
Try this:
Code:
SELECT U.Code, Sum(U.sCost) AS SumOfCost
FROM (SELECT Code, IIf(Cost > 20, Cost, 0) AS sCost
FROM Table_1
UNION ALL
SELECT Code, IIf(Cost > 20, Cost, 0) AS sCost
FROM Table_2)  AS U
GROUP BY U.Code;

8. Registered User
Join Date
Feb 2010
Posts
22
Thank you so much! It helped me.

9. Moderator
Join Date
Mar 2009
Posts
5,442