CODEWITHSUNDEEP
sqldatabaseoracle-database
user1764351
user1764351

Reputation: 225

ORACLE find count based on two columns

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.

Upvotes: 1

Views: 335

Answers (4)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

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

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 1

Serg
Serg

Reputation: 22811

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.

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31889

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

Upvotes: 1

Related Questions