Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

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

Last updated: January 31, 2023 - 7:03 am UTC

Version: 11

Viewed 10K+ times! This question is

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 Chris 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

  (3 ratings)

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?

Niki, January 17, 2023 - 5:16 pm UTC

With the same inputs as the question above, how to get the sum of the count obtained from the pivot query?

Sample Output:

TKT_NAME OPEN CLOSE
--------------- ---------- ----------
bug 2 1
request 1 1
Sum 3 2
Chris Saxon
January 18, 2023 - 1:30 pm UTC

You can SUM with a ROLLUP

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

TCKTNAME       SUM(O)     SUM(C)        TOT
---------- ---------- ---------- ----------
abc                 1          0          1
bug                 2          1          3
request             0          1          1
<null>              3          2          5

mathguy, January 31, 2023 - 6:19 am UTC

Rather than PIVOT (an aggregate operation) and then aggregate again for the rollup, it is more efficient to do away with the PIVOT syntax and use the old way of pivoting, which is just conditional aggregation. The whole thing can be done in a single aggregate query:

select case grouping(tcktname) when 0 then tcktname else ' TOTAL' end as tcktname,
       count(case status when 'open'  then 1 end) as open_ct,
       count(case status when 'close' then 1 end) as close_ct,
       count(*) as total_ct
from   ticket1
group  by rollup (tcktname)
;

TCKTNAME      OPEN_CT   CLOSE_CT   TOTAL_CT
---------- ---------- ---------- ----------
abc                 1          0          1
bug                 2          1          3
request             0          1          1
 TOTAL              3          2          5

Connor McDonald
January 31, 2023 - 7:03 am UTC

Agreed. We were just working with the original solution and evolving that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.