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.

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);

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;

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.

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.

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).

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