Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

group by value count and other count to show

suman amaraDec 30 2020

Hi Team
i have scenario having data like below
create table temp1 (quote_no number,status varchar(10), review_status varchar(2));
insert into temp1 values(1,'Open',Null);
insert into temp1 values(2,'Open','Y');
insert into temp1 values(5,'Open','Y');

insert into temp1 values(3,'Close',Null);
insert into temp1 values(4,'Close','Y');
insert into temp1 values(9,'Close',Null);
insert into temp1 values(11,'Close',Null);
insert into temp1 values(12,'Close','Y');

select distinct status,count(*) col1 from temp1
group by status
;
o/p
status ---col1
open ----3
close ----5
but i need to result like using group by the log of status and review_status is Y count.
open ----3 -- 2
close ----5---2

Comments

BEDE

select distinct status,count(*) col1
,sum(
case
when reviwe_status='Y' then 1
else 0
end
) number_reviewed
from temp1
group by status
;

Frank Kulash

Hi,
SUM (CASE ...), as Bede showed, will work. You could also use COUNT (CASE ...), like this:

SELECT    status              -- or  LOWER (status) AS status
,         COUNT (*)  AS col1  -- or a more descriptive name, like number_total
,	  COUNT ( CASE
	  	      WHEN  review_status = 'Y'
		      THEN  'OK'
	  	  END
	  	)    AS number_reviewed
FROM      temp1
GROUP BY  status
ORDER BY  status  -- or whatever
;

If you really want to display 'open' and 'close' even though the table has 'Open' and 'Close', as you said, then you just need to change the first line.
In any event, you don't need "SELECT DISTINCT". The GROUP BY clause guarantees that status will be distinct in the output

1 - 2

Post Details

Added on Dec 30 2020
#sql
2 comments
116 views