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

    Unanswered: Need help with Union query

    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
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly you need a group by here:

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

    UNION

    SELECT ClientID, sum(ABC), count(*)
    FROM Table B
    Where <condition>
    Group by clientID

    The Group by at the end does not do anything. This can be removed. Unless you need have client information in both tables. If this is the case then you need to do something different as follows:

    SELECT ClientID, SUM(ABC), COUNT(*) FROM (

    SELECT ClientID, ABC
    FROM Table A
    Where <condition>

    UNION

    SELECT ClientID, ABC
    FROM Table B
    Where <condition> )a, ClientID b

    WHERE a.ClientID = b.ClientID

    group by ClientID
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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