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

    Unanswered: union, count, and group by problem

    I am having a problem with a UNION. Separately, these two results work and return what I expect. When I add the UNION, I do not get the results I want.

    Here is what I should get

    GROUP_ID : marketing
    TOTAL_COUNT : 4
    LATE_COUNT : 2

    Instead, this is what I am getting

    GROUP_ID : marketing
    TOTAL_COUNT : 4
    GROUP_ID : marketing
    TOTAL_COUNT : 2

    My variable LATE_COUNT does not show up, but the value does, and the GROUP_ID shows up twice, when I want it to show once.


    SELECT count(group_id) as total_count, group_id
    FROM hrpy.hr_online_transactions
    WHERE flsa_status = '#form.flsa_status#'
    GROUP BY group_id
    UNION
    SELECT count(group_id) as late_count, group_id
    FROM hrpy.hr_online_transactions
    WHERE flsa_status = '#form.flsa_status#' AND end_date = variables.begin_dt
    GROUP BY group_id
    ORDER BY group_id

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    those are the correct results for a union.

    your options are:
    - write a single query using complex decodes (or case) to get both values on one row (since both queries are almost identical)
    - outer-join you're two queries to each other - late_Count is a subset of total_count, so total_count is the driving query
    - use in-line views to join the two separate queries.

    I prefer the first approach:
    SELECT group_id,
    count(group_id) total_count,
    sum( decode(end_date,variables.begin_dt,1,0) ) late_count
    FROM hrpy.hr_online_transactions
    WHERE flsa_status = '#form.flsa_status#'
    GROUP BY group_id
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do this:
    Code:
    SELECT count(group_id) as total_count, 
           count(case when end_date = variables.begin_dt then group_id end) as late_count,
           group_id
    FROM   hrpy.hr_online_transactions
    WHERE  flsa_status = '#form.flsa_status#'
    GROUP BY group_id

Posting Permissions

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