| How to group a multi select? [message #2055] |
Wed, 19 June 2002 06:39  |
sorcer
Messages: 19
Registered: January 2002
|
Junior Member |
|
|
Hello,
I have the T2 table with this data :
T2_CA#T2_ID#T2_NO
120#1#1
120#1#1
110#1#2
110#1#2
220#2#3
220#2#3
230#2#4
230#2#4
And when i execute this SQL query :
SELECT T2_ID,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '1') AS AUI,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '2') AS ABS,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '3') AS INF,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '4') AS POP
FROM T2
I obtain this result :
T2_ID#AUI#ABS#INF#POP
1#240#220#440#460
1#240#220#440#460
1#240#220#440#460
2#240#220#440#460
2#240#220#440#460
2#240#220#440#460
2#240#220#440#460
1#240#220#440#460
And i want to group by T2_ID,AUI,ABS,INF,POP but i can't group in the select, i have an Oracle error.
I want this result :
T2_ID#AUI#ABS#INF#POP
1#240#220#440#460
2#240#220#440#460
Can someone help me?
Thank you very much.
|
|
|
|
| Re: How to group a multi select? [message #2056 is a reply to message #2055] |
Wed, 19 June 2002 07:13  |
sorcer
Messages: 19
Registered: January 2002
|
Junior Member |
|
|
Like that :
SELECT DISTINCT A.T2_ID,
(SELECT SUM(T2_CA) FROM T2 WHERE T2_ID = A.T2_ID AND T2_NO = 1),
(SELECT SUM(T2_CA) FROM T2 WHERE T2_ID = A.T2_ID AND T2_NO = 2),
(SELECT SUM(T2_CA) FROM T2 WHERE T2_ID = A.T2_ID AND T2_NO = 3),
(SELECT SUM(T2_CA) FROM T2 WHERE T2_ID = A.T2_ID AND T2_NO = 4)
FROM T2 A
Result :
1#240#220#NULL#NULL
2#NULL#NULL#440#460
|
|
|
|
| Re: How to group a multi select? [message #2106 is a reply to message #2055] |
Thu, 20 June 2002 21:01 |
abirami
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
Hi,
Use this query to get the result.
SELECT temp.t2_id,temp.aui,temp.abs,temp.inf,temp.pop
FROM
(
SELECT T2_ID,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '1') AS AUI,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '2') AS ABS,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '3') AS INF,
(SELECT SUM(T2_CA) FROM T2
WHERE T2_NO = '4') AS POP
FROM T2)
ORDER BY temp.ts_id,temp.AUI,temp,temp.abs,temp.inf,temp.pop;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
