Home » RDBMS Server » Performance Tuning » Output display
| Output display [message #65949] |
Fri, 04 February 2005 11:50  |
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  |
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 #65957 is a reply to message #65950] |
Sun, 06 February 2005 15:08 |
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 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Mar 06 11:41:36 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
