Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Can I use GROUP BY with UNION?

    For example, would this work?

    SELECT text1,sum(amount1)
    FROM tbl
    GROUP BY txt1

    UNION ALL

    SELECT text1,sum(amount1)
    FROM tbl2
    GROUP BY txt1

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    is there some inexplicable reason that you cannot run the statement to find out if it works?

    would probably take you 1 second.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would think that this statement will FUSS about the group by column ...

    SELECT text1,sum(amount1)
    FROM tbl
    GROUP BY txt1 <-- txt1 probably should be text1

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: Can I use GROUP BY with UNION?

    Originally posted by racerxm3
    For example, would this work?

    SELECT text1,sum(amount1)
    FROM tbl
    GROUP BY txt1

    UNION ALL

    SELECT text1,sum(amount1)
    FROM tbl2
    GROUP BY txt1
    Yes
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Why not take out the ambiguity? The fact that you ask means it is ambiguous, the fact that it's ambiguous means that even different versions of the same parser might treat it differently. If you've ever tried to write a parser you'll know what I mean.

    PHP Code:
    select from
       
    (
       
    SELECT text1,sum(amount1)
       
    FROM tbl
       GROUP BY txt1
       
    )
    UNION ALL
       
    (
       
    SELECT text1,sum(amount1)
       
    FROM tbl2
       GROUP BY text1
       

    The Oracle optimiser does a good job of taking out redundancy, but not such a wonderful job with ambiguous code.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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