Forum Stats

  • 3,724,109 Users
  • 2,244,689 Discussions
  • 7,850,864 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

group by value count and other count to show

User_7PZDE
User_7PZDE Member Posts: 280 Blue Ribbon

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




 

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,205 Silver Trophy

    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
    Frank Kulash Member, Moderator Posts: 40,210 Red Diamond
    edited December 2020

    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

Sign In or Register to comment.