Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Can I use Group By, Count, and Union?

    I have two tables that have the same structure, but aren't related to each other. Each row in both tables as a field called "group_id", which is a name that is given to my users.

    I'm trying to get a total count for each group within the two tables. The problem, is, my code below will count, but it won't union the two results. So I get

    Group A 12
    Group A 23
    Group B 2
    Group B 1

    *****************************
    SELECT count(group_id) as bob, group_id
    FROM trans_additionalPay
    group by group_id

    UNION
    SELECT count(group_id) as bob, group_id
    FROM trans_basePay
    group by group_id
    order by group_id
    *****************************

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select sum(bob) as sumbob
         , group_id
      from (     
           SELECT count(group_id) as bob, group_id
           FROM trans_additionalPay
           group by group_id
           UNION ALL
           SELECT count(group_id) as bob, group_id
           FROM trans_basePay
           group by group_id
           ) as U
    group
        by group_id                
    order
        by group_id
    note: you have to use UNION ALL in case some group has the same count in both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    That worked! Thank you.

    I was wondering if you could talk me through what is going on w/ that query. For instance, why use UNION ALL instead of UNION and why do you need a sum outside the two querys.

    Thanx again!!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    talk you through it? sure, feel free to call me at (416) 751-0937, normal business hours, eastern time zone, north america

    UNION removes duplicates, so if some group has a count of 4 in the first table, and a count of 4 in the second table, UNION will give you only one row with a count of 4

    the "sum outside the two queries" is to combine the two rows for each group into one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    USA
    Posts
    28
    r937 is 100% correct, 'UNION' and 'UNION ALL' are both Set operators. UNION returns all rows from either queries; and no duplicate rows. where as 'UNION ALL' returns all rows from either queries; including duplicates.
    Last edited by GorkhaliDBA; 06-12-04 at 20:46.
    ___________
    GorkhaliOCA
    USA

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Alternatively,

    select group_id, count(group_id) + (select count(group_id) from tableB where group_id = ta.group_id)
    from tableA ta
    group by ta.group_id;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jul 2013
    Posts
    1

    Red face

    Quote Originally Posted by r937 View Post
    the "sum outside the two queries" is to combine the two rows for each group into one
    This solves my problem if I am only using the two columns, bob and the group_id, but as soon as I add more columns, I get duplicates again. WHY???

    Any ideas on how to get around that? Maybe create a temp then update the temp adding new columns later??

Posting Permissions

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