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

    Question Unanswered: union & grouping

    Hello,

    I am querying one table, as follows:

    SELECT TRUNC(created_dt) CREATED_DT,
    SUM (DECODE (item_group_status_id, 6, 1, 0)) "IN_ANALYSIS"
    FROM ITEM_GROUPS
    WHERE item_group_category_id = 3
    GROUP BY TRUNC(created_dt) InAnalysis;

    But I also need the results that are "Pending",

    SELECT TRUNC(created_dt) CREATED_DT,
    SUM (DECODE (item_group_status_id, 1, 1, 0)) "PENDING"
    FROM ITEM_GROUPS
    WHERE item_group_category_id = 3
    AND item_group_status_id=1
    GROUP BY TRUNC(created_dt) Pending;

    What I want to do is join both queries together and have 3 columns (Date, Pending, In Analysis). I want to group it by date and count how many rows are in Pending and In Analysis. Can someone please leed me in the right direction? Thanks a lot.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: union & grouping

    SELECT TRUNC(created_dt) CREATED_DT,
    SUM (DECODE (item_group_status_id, 6, 1, 0)) "IN_ANALYSIS",
    SUM (DECODE (item_group_status_id, 1, 1, 0)) "PENDING"
    FROM ITEM_GROUPS
    WHERE item_group_category_id = 3
    AND item_group_status_id IN (1,6)
    GROUP BY TRUNC(created_dt);
    Last edited by andrewst; 01-28-04 at 11:59.

  3. #3
    Join Date
    Jan 2004
    Posts
    4

    Re: union & grouping

    Hi,

    I did try that, but the statement

    AND item_group_status_id=1

    is missing and this needs only to be used for the Pending, not for the Analysis

    The results for In_Analysis is

    Created_Dt In_Analysis
    1/17/2004 4
    1/19/2004 0
    1/20/2004 0
    1/21/2004 3
    1/26/2004 0

    The results for Pending is

    Created_Dt Pending
    1/20/2004 3
    1/21/2004 5
    1/26/2004 1

    If I use the query that you provided then the 1st row from the Pending results won't be listed. This is what we had before and when I inserted the "AND item_group_status_id=1" for the Pending, it still didn't work :

    (SELECT TRUNC (created_dt) created_dt,
    SUM (DECODE (item_group_status_id, 1, 1, 0)) "PENDING"
    FROM ITEM_GROUPS
    WHERE item_group_category_id = 3
    GROUP BY TRUNC (created_dt)) Pending,
    (SELECT TRUNC (cust.Transaction_DT) transaction_dt,
    SUM (DECODE (item_group_status_id, 4, 1, 0)) "IN_STORAGE",
    SUM (DECODE (item_group_status_id, 5, 1, 0)) "IN_TRANSIT",
    SUM (DECODE (item_group_status_id, 6, 1, 0)) "IN_ANALYSIS"
    FROM ITEM_GROUPS,
    (SELECT *
    FROM CUSTODIES
    WHERE closed_dt IS NULL) cust
    WHERE cust.item_group_id = ITEM_GROUPS.item_group_id
    AND ITEM_GROUPS.item_group_category_id = 3 -- Evidence
    GROUP BY TRUNC (cust.Transaction_DT)) Other
    WHERE pending.created_dt = other.transaction_dt;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: union & grouping

    How could my removing a condition result in a missing record? (It can't) I think you'll find my SQL works. It's a standard "pivot" query.

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    Yes, your query works, but it doesn't give the results for the ones that are in Pending.

    The statement ITEM_GROUP_CATEGORY_ID=3 (is for an envelope) & and statement ITEM_GROUP_STATUS_ID=1 (is for the status Pending). Does that help? Maybe I'm not explaining myself properly. Let me know, Thanks.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Show me some sample input data and output. I don't see why it wouldn't work. I have corrected my earlier SQL adding a missing comma, and for good measure added a WHERE clause to restrict to the 2 status codes of interest (1,6).

  7. #7
    Join Date
    Jan 2004
    Posts
    4

    Thumbs up

    It works like a charm! I missed the statement

    AND item_group_status_id IN (1,6)

    That you added. Thanks so much, I really appreciate it!

    Cheers

Posting Permissions

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