Home » SQL & PL/SQL » SQL & PL/SQL » Cross tab SQL query (Oracle 9i)
Cross tab SQL query [message #638612] Mon, 15 June 2015 20:00 Go to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi,

I am looking for a cross tab query in sql. The requirement is to have summary of tickets created in past 24 hrs.
The desired output should look as below
------------------------------------------------------------------

Status	                      P1	P2	P3	P4
Total Tickets	               0	8	4	0
Open Tickets	               0	5	3	0
Resolved Tickets	       0	3	1	0
Average of Resolve tickets     0	45	0	0
------------------------------------------------------------------

Here average should be calculated only on resolved tickets (sum(abc.field1)/count(resolved tickets)). 

Below is my query

SELECT ( CASE
           WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
           WHEN status = '4' THEN 'Resolved'
           ELSE 'Total'
         END )                           AS status,
       Count(( CASE
                 WHEN priority = '3'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = '3'
                      AND status = '4' THEN 1
               END ))                    AS P1,
       Count(( CASE
                 WHEN priority = '2'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = '2'
                      AND status = '4' THEN 1
               END ))                    AS P2,
       Count(( CASE
                 WHEN priority = '1'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = '1'
                      AND status = '4' THEN 1
               END ))                    AS P3,
       Count(( CASE
                 WHEN priority = '0'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = '0'
                      AND status = '4' THEN 1
               END ))                    AS P4,
       Nvl(( SUM(CASE
                   WHEN ( priority = '3'
                          AND status = '4' ) THEN abc.field1
                 END) / Count(CASE
                                WHEN ( priority = '3'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP1,
       Nvl(( SUM(CASE
                   WHEN ( priority = '2'
                          AND status = '4' ) THEN abc.field1
                 END) / Count(CASE
                                WHEN ( priority = '2'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP2,
       Nvl(( SUM(CASE
                   WHEN ( priority = '1'
                          AND status = '4' ) THEN abc.field2
                 END) / Count(CASE
                                WHEN ( priority = '1'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP3,
       Nvl(( SUM(CASE
                   WHEN ( priority = '0'
                          AND status = '4' ) THEN abc.field2
                 END) / Count(CASE
                                WHEN ( priority = '0'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP4
FROM   abc
WHERE  ( create_date >= To_date(SYSDATE + interval '-24' hour)
         AND create_date < SYSDATE )
       AND status IN ( '0', '1', '2', '3',
                       '4', '6' )
GROUP  BY rollup ( CASE
              WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
              WHEN status = '4' THEN 'Resolved'
              ELSE 'Total'
            END ) 

My current output
------------------

STATUS                        P1         P2         P3         P4      AvgP1      AvgP2      AvgP3      AvgP4
--------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
Open Tickets (24 hrs)          0          5          3          0          0          0          0          0
Resolved                       0          3          1          0          0         45          0          0
                               0          8          4          0          0         45          0          0

Please suggest how to fetch the desired output in oracle sql.

Regards,
Tara


[Updated on: Mon, 15 June 2015 21:40]

Report message to a moderator

Re: Cross tab SQL query [message #638614 is a reply to message #638612] Tue, 16 June 2015 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data.

Re: Cross tab SQL query [message #638616 is a reply to message #638614] Tue, 16 June 2015 02:33 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi,

Please find the update query and attached is the test case script.

SELECT ( CASE  WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
           WHEN status = '4' THEN 'Resolved'
           ELSE 'Total'
         END )  AS status,
       Count(( CASE
                 WHEN priority = 'P1'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = 'P1'
                      AND status = '4' THEN 1
               END ))                    AS P1,
       Count(( CASE
                 WHEN priority = 'P2'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = 'P2'
                      AND status = '4' THEN 1
               END ))                    AS P2,
       Count(( CASE
                 WHEN priority = 'P3'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = 'P3'
                      AND status = '4' THEN 1
               END ))                    AS P3,
       Count(( CASE
                 WHEN priority = 'P4'
                      AND status IN ( '0', '1', '2', '3', '6' ) THEN 1
                 WHEN priority = 'P4'
                      AND status = '4' THEN 1
               END ))                    AS P4,
       Nvl(( SUM(CASE
                   WHEN ( priority = 'P1'
                          AND status = '4' ) THEN field1
                 END) / Count(CASE
                                WHEN ( priority = 'P1'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP1,
       Nvl(( SUM(CASE
                   WHEN ( priority = 'P2'
                          AND status = '4' ) THEN field1
                 END) / Count(CASE
                                WHEN ( priority = 'P2'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP2,
       Nvl(( SUM(CASE
                   WHEN ( priority = 'P3'
                          AND status = '4' ) THEN field2
                 END) / Count(CASE
                                WHEN ( priority = 'P3'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP3,
       Nvl(( SUM(CASE
                   WHEN ( priority = 'P4'
                          AND status = '4' ) THEN field2
                 END) / Count(CASE
                                WHEN ( priority = 'P4'
                                       AND status = '4' ) THEN 1
                              END) ), 0) AvgP4
FROM   SPTEST
WHERE   (create_date >= '14-JUN-2015'
      AND create_date < SYSDATE )
     and   status IN ( '0', '1', '2', '3',
                       '4', '6' )
GROUP  BY cube ( CASE
              WHEN status IN ( '0', '1', '2', '3', '6' ) THEN 'Open Tickets'
              WHEN status = '4' THEN 'Resolved'
              ELSE 'Total'
            END ) 
            
query output
------------
"STATUS"	"P1"	"P2"	"P3"	"P4"	"AVGP1"	"AVGP2"	"AVGP3"	"AVGP4"
NULL		1	6	4	4	8	7.5	5	4
"Resolved"	1	2	1	1	8	7.5	5	4
"Open Tickets"	0	4	3	3	0	0	0	0


Desired Output
--------------

"STATUS"	"P1"	"P2"	"P3"	"P4"	
NULL		1	6	4	4	
"Resolved"	1	2	1	1	
"Open Tickets"	0	4	3	3	
AVG		8	7.5	5	4



Thanks in advance
  • Attachment: export.sql
    (Size: 2.94KB, Downloaded 1301 times)
Re: Cross tab SQL query [message #638620 is a reply to message #638616] Tue, 16 June 2015 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to explain your desired result.
How do you compute AVG line?

Re: Cross tab SQL query [message #638640 is a reply to message #638620] Tue, 16 June 2015 06:39 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi Michel,

Average is calculated based only on resolved tickets.

Here average for P1 tickets = (sum(field1)/count(resolved tickets of P1))
average for P2 tickets = (sum(field1)/count(resolved tickets of P2))
average for P3 tickets = (sum(field2)/count(resolved tickets of P3))
average for P4 tickets = (sum(field2)/count(resolved tickets of P4))

ex: in test data field1 value for P2 (only resolved tickets) is 10 and 5, count(resolved tickets of P2) is 2;
so average will be (sum(10+5)/2) = 7.5


Thanks,
Tara

[Updated on: Tue, 16 June 2015 06:50]

Report message to a moderator

Re: Cross tab SQL query [message #638641 is a reply to message #638640] Tue, 16 June 2015 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have no more 9i database to test the query but I think I didn't use any function or syntax not existing in this version.
SQL> with
  2    data as (
  3      select priority,
  4             case
  5               when status in ( '0', '1', '2', '3', '6' ) then 'Open Tickets'
  6               when status ='4' then 'Resolved'
  7             end status,
  8             count(*) nb_status,
  9             count(decode(status, '4', 1)) nb_resolved,
 10             sum(case
 11                   when status ='4' and priority in ('P1', 'P2') then field1
 12                   when status ='4' and priority in ('P3', 'P4') then field2
 13                   else 0
 14                 end) sum_field,
 15             sum(count(*)) over (partition by priority) total
 16      from sptest
 17      where create_date between to_date('14-JUN-2015','DD-MON-YYYY') and sysdate
 18        and status in ( '0', '1', '2', '3', '4', '6' )
 19      group by priority,
 20               case
 21                 when status in ( '0', '1', '2', '3', '6' ) then 'Open Tickets'
 22                 when status ='4' then 'Resolved'
 23               end
 24    ),
 25    lines as (select level line from dual connect by level <= 4)
 26  select decode(line, 1,'Total', 2,'Resolved', 3,'Open Tickets', 4,'Average') status,
 27         decode(line,
 28                1, max(decode(priority, 'P1',total)),
 29                2, max(decode(priority, 'P1',decode(status,'Resolved',nb_status))),
 30                3, max(decode(priority, 'P1',decode(status,'Open Tickets',nb_status))),
 31                4, max(decode(priority, 'P1',sum_field/greatest(nb_resolved,1)))
 32               ) "P1",
 33         decode(line,
 34                1, max(decode(priority, 'P2',total)),
 35                2, max(decode(priority, 'P2',decode(status,'Resolved',nb_status))),
 36                3, max(decode(priority, 'P2',decode(status,'Open Tickets',nb_status))),
 37                4, max(decode(priority, 'P2',sum_field/greatest(nb_resolved,1)))
 38               ) "P2",
 39         decode(line,
 40                1, max(decode(priority, 'P3',total)),
 41                2, max(decode(priority, 'P3',decode(status,'Resolved',nb_status))),
 42                3, max(decode(priority, 'P3',decode(status,'Open Tickets',nb_status))),
 43                4, max(decode(priority, 'P3',sum_field/greatest(nb_resolved,1)))
 44               ) "P3",
 45         decode(line,
 46                1, max(decode(priority, 'P4',total)),
 47                2, max(decode(priority, 'P4',decode(status,'Resolved',nb_status))),
 48                3, max(decode(priority, 'P4',decode(status,'Open Tickets',nb_status))),
 49                4, max(decode(priority, 'P4',sum_field/greatest(nb_resolved,1)))
 50               ) "P4"
 51  from data, lines
 52  group by line
 53  order by line
 54  /
STATUS               P1         P2         P3         P4
------------ ---------- ---------- ---------- ----------
Total                 1          6          4          4
Resolved              1          2          1          1
Open Tickets                     4          3          3
Average               8        7.5          5          4

I let you add the NVL function if you want 0 instead of NULL when there is no tickets of some kinds.

[Updated on: Tue, 16 June 2015 07:18]

Report message to a moderator

Re: Cross tab SQL query [message #638645 is a reply to message #638641] Tue, 16 June 2015 14:22 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi Michel,

Thanks a lot for your help,it worked perfect.

~Tara
Re: Cross tab SQL query [message #638669 is a reply to message #638645] Wed, 17 June 2015 12:29 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi Experts,

Can you please help me to convert above sql query to stored procedure, i am very new to stored procedures.

Thanks
Re: Cross tab SQL query [message #638670 is a reply to message #638669] Wed, 17 June 2015 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Never do in PL/SQL something you can do in SQL.
What is your problem with SQL?
What stored procedure do you want?

Re: Cross tab SQL query [message #638671 is a reply to message #638669] Wed, 17 June 2015 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> DECLARE
  2  NOW DATE;
  3  BEGIN
  4  DBMS_OUTPUT.ENABLE(32000);
  5  SELECT SYSDATE INTO NOW FROM DUAL;
  6  DBMS_OUTPUT.PUT_LINE(NOW);
  7  DBMS_OUTPUT.PUT_LINE('I AM TOO LAZY TO READ THE FINE MANUAL!');
  8  END;
  9  /
17-JUN-15
I AM TOO LAZY TO READ THE FINE MANUAL!

PL/SQL procedure successfully completed.


Re: Cross tab SQL query [message #638672 is a reply to message #638670] Wed, 17 June 2015 13:59 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Hi Michel,

This query is working perfectly fine at oracle end, the issue is when the same query is executed in linked server we are facing problems, which i believe is due to CTE used in query. That's the reason our reporting team is asking for a stored procedure with parameters.

Kindly suggest.

Thanks,
Tara
Re: Cross tab SQL query [message #638673 is a reply to message #638671] Wed, 17 June 2015 14:02 Go to previous messageGo to next message
tarasasu
Messages: 9
Registered: December 2011
Junior Member
Thanks BlackSwan for introducing me to pl/sql with nice example.


Thanks,
Tara
Re: Cross tab SQL query [message #638674 is a reply to message #638672] Wed, 17 June 2015 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
the same query is executed in linked server


What is a "linked server"?

Quote:
we are facing problems


Which help us in no way to what is the "problem".

Quote:
which i believe is due to CTE used in query


Sorry, I don't know what is CTE.

Quote:
That's the reason our reporting team is asking for a stored procedure with parameters


Without knowing what are the inputs and outputs of the procedure we can't help more.
Post the specification.

[Updated on: Wed, 17 June 2015 14:06]

Report message to a moderator

Re: Cross tab SQL query [message #638722 is a reply to message #638674] Fri, 19 June 2015 03:23 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
This 'smells' like the query is actually getting executed on SQL Server.
Previous Topic: Function declartation
Next Topic: Split rows into columns
Goto Forum:
  


Current Time: Thu Mar 28 19:29:50 CDT 2024