Oracle SQL help counts the same thing in two different ways, but with a common grouping

Question

I have a table that contains for each entry incident_id , status (say, either open or closed , date_raised (Date) and closure_date (Date).

I want to show a table that counts the number of incidents closed by closing date (so that the incident_id counter is where status='closed' and closure_date is not null ) and the number of incidents that remain open (number of incident_id where status='open' on the same day.

In case I confused you, the table looks like this:

  ______________________________________________________________________________ | closure date | count of incidents closed | count of incidents remaining open | |--------------|---------------------------|-----------------------------------| | 01-Sep-12 | 5 | 14 | | ... | ... | ... | 

I dealt with a table in which the number of incidents is closed as follows:

 SELECT COUNT(incident_id) WHERE closure_date IS NOT NULL AND status="open" GROUP BY closure_date 

I tried for several hours to get another account to work, but I can’t: - (

Edit: Here is an example table that I have:

  ___________________________________________________ | incident_id | status | date_raised | closure_date | |-------------|--------|-------------|--------------| | 1 | closed | 01-Sep-12 | 01-Sep-12 | | 2 | open | 30-Aug-12 | (null) | | 3 | open | 02-Sep-12 | (null) | | 4 | closed | 02-Sep-12 | 05-Sep-12 | | ... | ... | ... | ... | 

Gives a table:

  ______________________________________________________________________________ | closure date | count of incidents closed | count of incidents remaining open | |--------------|---------------------------|-----------------------------------| | 01-Sep-12 | 1 | 1 | | 05-Sep-12 | 1 | 2 | 

Answer 1

It seems to me that for each date you want to get the number of problems that have been closed to date, as well as the number of problems that are still open that have been raised before this date, right? Therefore, you may need something like this:

 SELECT t1.closure_date, COUNT(t1.incident_id) , ( SELECT COUNT(t2.incident_id) FROM incident_table t2 WHERE t2.status = 'open' AND t2.raised_date < t1.closure_date ) FROM incident_table t1 WHERE t1.closure_date IS NOT NULL AND t1.status = 'closed' GROUP BY t1.closure_date 

Answer 2

 SELECT COUNT(*) as closed, (SELECT COUNT(t2.incident_id) FROM TABLE t2 WHERE t2.status = 'open' and t2.raised_date < t1.closure_date) as open FROM TABLE t1 WHERE t1.status = 'closed' GROUP BY t1.closure_date 

or idea

 WITH opened AS (SELECT COUNT(t2.incident_id) as cnt FROM table t2 WHERE STATUS = 'open' ) SELECT to_char(closure_date, 'yyyy/mm/dd') as dte count(*) as closed, opened.cnt as opens FROM table, opened WHERE status = 'closed' GROUP BY to_char(closure_date, 'yyyy/mm/dd'), opened.cnt 

Answer 3

You should try something like this:

 SELECT closure_date, SUM(CASE WHEN status="open" THEN 1 ELSE 0 END) AS "count of incidents open", SUM(CASE WHEN status="closed" THEN 1 ELSE 0 END) AS "count of incidents closed" FROM YourTable WHERE closure_date IS NOT NULL GROUP BY closure_date