ORACLE find count based on two columns

Question

I have an Oracle database Table where

I have many columns in this particular table, but i want to get result on basis of computation between two columns as described below

Project|Status
-------|--------
 1     | Done    
 1     | Pending
 2     | Done

I want to get count of pending projects, for example if project 1 have a status pending and also it don't have done status anywhere in the table it will be a pending task, but if project 1 have a pending status and also have a done status in the table in any other row then this will not be a pending task,

I have tried this query but it is returning rows which have both pending and done status,

SELECT * FROM MYTABLE T 
WHERE EXISTS
(SELECT 1 FROM MYTABLE WHERE Project = A.Project AND ROWID < A.ROWID AND            
Status ='Done') 
AND T.Status!='Done' AND T.Status='Pending'

@Update I also have other status values in this column like 'Partially Done' and 'Requested' and so i want to get only those projects which have only pending status and no 'Done' Status in whole table.

Unnikrishnan R · Accepted Answer · 2016-10-07 10:04:53Z

1

If you wanted to get the pending projects and which do not have another status 'Done',Use the below query.

SELECT * FROM MYTABLE T 
WHERE T.Status='Pending'
AND NOT EXISTS --excluding projects with 'done' status
(SELECT 1 FROM MYTABLE A WHERE A.Project = T.Project AND         
A.Status ='Done')

If you wanted to get the count of total pending projects ,use the below script.

 SELECT COUNT(*) FROM MYTABLE T 
 WHERE T.Status='Pending'
    AND NOT EXISTS
    (SELECT 1 FROM MYTABLE A WHERE A.Project = T.Project AND         
    A.Status ='Done')

edited Oct 7, 2016 at 10:04

answered Oct 7, 2016 at 9:44

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1764351 Over a year ago

That's what i was looking for. Thank you

Serg Over a year ago

Check the aliases.

Felix Pamittan · Accepted Answer · 2016-10-07 09:36:08Z

1

You can do this using HAVING and GROUP BY:

SELECT
    Project
FROM MyTable t
GROUP BY Project
HAVING
    -- Should have at least one Pending status
    SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) > 0 
    -- No other status aside from Pending
    AND SUM(CASE WHEN Status <> 'Pending' THEN 1 ELSE 0 END) = 0

answered Oct 7, 2016 at 9:36

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Comments

Serg · Accepted Answer · 2016-10-07 09:45:36Z

1

One way is NOT EXISTS

SELECT * FROM MYTABLE T 
WHERE NOT EXISTS
   (SELECT 1 
    FROM MYTABLE T2 
    WHERE T2.Project = T.Project AND T.ROWID < T2.ROWID 
         AND T2.Status ='Done') 
AND Status='Pending'

Not sure on AND T.ROWID < T2.ROWID . I'd better used a datetime column indicating the date of status became actual. Also depending of your task it can be reversed AND T.ROWID > T2.ROWID.

answered Oct 7, 2016 at 9:45

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Comments

Jaydip Jadhav · Accepted Answer · 2016-10-07 09:52:00Z

1

Try this script : this will count project whose status is only Pending

SELECT Project,COUNT(Project) 
FROM MYTABLE t1 
WHERE NOT EXISTS(SELECT 1 FROM MYTABLE t2 WHERE t2.Project = t1.Project AND T2.Status='Done') 
AND t1.Status='Pending' 
GROUP BY Project

edited Oct 7, 2016 at 9:52

answered Oct 7, 2016 at 9:43

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

ORACLE find count based on two columns

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related