CODEWITHSUNDEEP
sqloracle-databasegroup-by
CodeMonkey
CodeMonkey

Reputation: 3

Count and Group By Date but also have 3rd WHERE column

I have table like this:

CREATED         STATUS
--------------------------------    
12-Dec-20       Pending 
12-Dec-20       OnHold  
12-Dec-20       Pending 
12-Dec-20       OnHold  
12-Dec-20       Completed   
12-Dec-20       Completed   
13-Dec-20       Completed   
13-Dec-20       Completed   
14-Dec-20       Pending 
14-Dec-20       OnHold  
14-Dec-20       OnHold  
15-Dec-20       Pending 
15-Dec-20       Completed

Desired Output:

CREATED         TOTAL       COMPLETED
---------------------------------------
12-Dec-20       6           2
13-Dec-20       2           2
14-Dec-20       3           {0 or blanks are ok}
15-Dec-20       2           1

I can get first 2 columns with this:

select trunc(created), count(*)
from table1
group by trunc(created)

But I can't figure out how to add the 3rd column based on a WHERE STATUS like 'Completed' clause.

Do I need a Group By Set, or an Inner Join? My data set is small, < 100,000 items. Simplicity is preferred over performance for this query.

Upvotes: 0

Views: 48

Answers (2)

blitzkopf
blitzkopf

Reputation: 61

This would be a nice use case for the pivot operator:

select * 
from data 
pivot ( count(*) as count 
   for (status) in (
      'Pending' AS pending, 
      'OnHold' AS onhold, 
      'Completed' AS completed
   ) 
);
CREATED PENDING_COUNT ONHOLD_COUNT COMPLETED_COUNT
12-DEC-20 2 2 2
14-DEC-20 1 2 0
15-DEC-20 1 0 1
13-DEC-20 0 0 2

If you really need the total on the row it get a little more complicated

select * 
from ( 
  select created,status,count(*) over( partition by created) total_count 
  from data )
pivot ( count(*) as count 
for (status) in (
  'Pending' AS pending, 
  'OnHold' AS onhold, 
  'Completed' AS completed
 ) ) 
 order by created;
CREATED TOTAL_COUNT PENDING_COUNT ONHOLD_COUNT COMPLETED_COUNT
12-DEC-20 6 2 2 2
13-DEC-20 2 1 2 0
14-DEC-20 3 1 0 1
15-DEC-20 4 0 0 2

You can play with it in this dbfiddle

Upvotes: 0

Divyaansh Bajpai
Divyaansh Bajpai

Reputation: 232

You can get using case statement :

select created , count(1) as Total , 
       sum(case when status = 'Completed' then 1 else 0 end ) as Completed
  from table1
 group by created

Upvotes: 1

Related Questions