Results 1 to 9 of 9
  1. #1
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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!
    Paul

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're right I did not think of that. Thanks!
    Have a nice day!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  6. #6
    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. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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;
    Have a nice day!

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

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •