Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 12, 2017 - 7:07 am UTC

Last updated: June 14, 2021 - 11:47 am UTC

Version: 11

Viewed 1000+ times

You Asked

create table ticket1
(ticketid number,
                      tcktname varchar2(10),
                       status  varchar2(10)
  );

INSERT INTO ticket1 VALUES (101,'bug','open');
INSERT INTO ticket1 VALUES (102,'bug','close');
INSERT INTO ticket1 VALUES (103,'bug','open');
INSERT INTO ticket1 VALUES (104,'request','close');
INSERT INTO ticket1 VALUES (105,'abc','open');

 select * from tkt;

TKT_NO TKT_NAME        STATUS
------ --------------- -------------
   101 bug             open
   102 bug             close
   103 bug             open
   104 request         close
   105 abc             close


when i ran my below code , i just got it this but mine was different requ.

select * from ( select tcktname , status from ticket1) 
  PIVOT 
  ( 
  Count(status) for status in('open',
                             'close')
                             )

TCKTNAME 'open' 'close'
bug             3    0


But i needed an COUNT also as per below output.

output:-

TKT_NAME              OPEN      CLOSE      TOTAL
--------------- ---------- ---------- ----------
bug                      2          1          3
request                  1          1          2


and we said...

I don't understand how those output numbers relate to the data you've provided.

But if you want the total, you just need to sum up the pivoted columns:

create table ticket1
(ticketid number,
tcktname varchar2(10),
status varchar2(10)
);

INSERT INTO ticket1 VALUES (101,'bug','open');
INSERT INTO ticket1 VALUES (102,'bug','close');
INSERT INTO ticket1 VALUES (103,'bug','open');
INSERT INTO ticket1 VALUES (104,'request','close');
INSERT INTO ticket1 VALUES (105,'abc','open');

select tcktname, o, c, o+c tot
from (
  select tcktname, status
  from ticket1
) pivot ( count ( status )
  for status in ( 'open' o,'close' c)
);

TCKTNAME            O          C        TOT
---------- ---------- ---------- ----------
bug                 2          1          3
request             0          1          1
abc                 1          0          1

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 12, 2021 - 5:35 am UTC

TKT_NAME OPEN CLOSE
--------------- ---------- ----------
bug 2 1
request 1 1
TOTAL 3 2

How to get like this answer
Chris Saxon
June 14, 2021 - 11:47 am UTC

And what's the input?