Home » RDBMS Server » Performance Tuning » Output display
Output display [message #65949] Fri, 04 February 2005 11:50 Go to next message
Ant
Messages: 13
Registered: February 2000
Junior Member
I have a table with value

cus Type Category
A 60 49
A 60 77
B 50 49
C 60 77
D 22 10

If I use decode function to define the category:
X=49
Y=77
Z=10
I would like to display the output as:

cus|X|Y|Z|Type60Tot|OtherTypeCount|TypeTot
A|1|1|0|2|0|2
B|0|0|0|0|1|1
C|0|1|0|1|0|1
D|0|0|0|0|1|1


Any clue what kind of query I should used? Thanks
Re: Output display [message #65950 is a reply to message #65949] Fri, 04 February 2005 20:29 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
select cus,
count(decode(category,49,1,0)) "X",
count(decode(category,77,1,0)) "Y",
count(decode(category,10,1,0)) "X"
count(decode(category,49,1,0))+count(decode(category,77,1,0))+count(decode(category,10,1,0)) "Total"
from table
group by cus
Re: Output display [message #65956 is a reply to message #65950] Sun, 06 February 2005 14:21 Go to previous messageGo to next message
Ant
Messages: 13
Registered: February 2000
Junior Member
Thanks for your reply. I tried and it didnt work..
Re: Output display [message #65957 is a reply to message #65950] Sun, 06 February 2005 15:08 Go to previous messageGo to next message
Ant
Messages: 13
Registered: February 2000
Junior Member
Could someone help pls

1 create table test
2 (cus VARCHAR2 (20),
3 type number,
4* category number)
SQL> /

Table created.

SQL> INSERT INTO test VALUES ('A',60,49);

1 row created.

SQL> INSERT INTO test VALUES ('A',60,77);

1 row created.

SQL> INSERT INTO test VALUES ('B',50,49);

1 row created.

SQL> INSERT INTO test VALUES ('C',60,77);

1 row created.

SQL> INSERT INTO test VALUES ('D',22,10);

1 row created.

SQL>

1 select cus,count(decode(category,49,1,0)) "X",
2 count(decode(category,77,1,0)) "Y",count(decode(category,10,1,0)) "Z"
3 sum(count(decode(category,49,1,0)),count(decode(category,77,1,0))) "Total"
4 from test
5* group by cus
SQL> /
sum(count(decode(category,49,1,0)),count(decode(category,77,1,0))) "Total"
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
Re: Output display [message #65958 is a reply to message #65957] Sun, 06 February 2005 20:48 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
SQL> select * from test;

CUS TYPE CATEGORY
-------------------- ---------- ----------
A 60 49
A 60 77
B 50 49
C 60 77
D 22 10

SQL> select cus,sum(decode(type,60,1,0)) X,sum(decode(type,50,1,0)) Y,sum(decode(type,22,1,0)) Z from test
group by cus;

CUS X Y Z
-------------------- ---------- ---------- ----------
A 2 0 0
B 0 1 0
C 1 0 0
D 0 0 1
Re: Output display [message #65959 is a reply to message #65956] Sun, 06 February 2005 21:05 Go to previous message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
SQL> select * from test;

CUS TYPE CATEGORY
-------------------- ---------- ----------
A 60 49
A 60 77
B 50 49
C 60 77
D 22 10

SQL> select cus,sum(decode(type,60,1,0)) X,sum(decode(type,50,1,0)) Y,sum(decode(type,22,1,0)) Z from test
group by cus;

CUS X Y Z
-------------------- ---------- ---------- ----------
A 2 0 0
B 0 1 0
C 1 0 0
D 0 0 1
Previous Topic: COMPUTE and ESTIMATE
Next Topic: Index not properly used when LIKE operator used
Goto Forum:
  


Current Time: Fri Mar 01 06:00:44 CST 2024