Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Unanswered: Union based on common column

    Hi All, I just joined this forum. Trying to figure out a SQL query.

    I have 2 identical tables: Table A and Table B

    This query doesn´t seem to be working:

    SELECT * FROM (

    SELECT ClientID, sum(ABC), count(*)
    FROM Table A
    Where <condition>

    UNION

    SELECT ClientID, sum(ABC), count(*)
    FROM Table B
    Where <condition> )a, ClientID b

    WHERE a.ClientID = b.ClientID

    group by ClientID


    The result I am looking for is that for each ClientID, I need the total sum of ABC and total count.

    Any suggestions?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't need a JOIN in a UNION query...but don't forget to GROUP BY ClientID, and to name your columns.

    SELECT ClientID, sum(ABC), as ClientSum, count(*) as ClientCount
    FROM Table A
    Where <condition>
    group by ClientID
    UNION
    SELECT ClientID, sum(ABC), as ClientSum, count(*) as ClientCount
    FROM Table B
    Where <condition>
    group by ClientID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    Perfect. This works. Thank you!!

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Read the materials at the front of this forum ..

    >> I have 2 identical tables: Table A and Table B <<

    This is a huge design flaw. In RDBMS, a table models a set of entities of the same kind. A set is the entire collection of those entities. If this was mathematics, would you have two sets of Integers? NO! Of course not!

    Since you did not post DDL or give sample data or follow any Netiquette, my guess is that when you correct the DDL, the query will simple be:

    SELECT client_id, SUM(abc), COUNT(*) AS client_cnt
    FROM Clients --- one table!
    WHERE <alpha condition>
    OR <beta condition>
    GROUP BY client_id;

    Did you remember that UNION removes redundant duplicate?

    Read the materials at the front of this forum and learn the basic rules. You will get much better answers and not just kludges.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    This is a huge design flaw.
    You are assuming that he created the database.

    Quote Originally Posted by Celko View Post
    Since you did not post DDL or give sample data or follow any Netiquette...
    You know absolutely nothing about Netiquette Joe.

    Quote Originally Posted by Celko View Post
    , my guess is that when you correct the DDL, the query will simple be:

    SELECT client_id, SUM(abc), COUNT(*) AS client_cnt
    FROM Clients --- one table!
    WHERE <alpha condition>
    OR <beta condition>
    GROUP BY client_id;
    Then your guess would be wrong, Joe.

    Quote Originally Posted by Celko View Post
    Read the materials at the front of this forum and learn the basic rules. You will get much better answers and not just kludges.
    Perhaps you are either to busy (or too lazy) to actually review the thread, but he already got his answer.
    You really embarrass yourself when you go on these childish tirades, Joe.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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