Complex Query [message #326975] |
Fri, 13 June 2008 04:49  |
vijay_tule
Messages: 1 Registered: June 2008
|
Junior Member |
|
|
Hi,
I am having a problem with getting result. The situation is like this.
I have one table named Tblcomplain which had columns comp_no, Prob_type,comp_status.The data is like this
Comp_no Comp_dept Prob_type Comp_status
1 IT Mouse Problem P
2 HR Keyboard Problem P
3 IT Mouse Problem S
4 Accounts Network Problem P
5 HR Monitor Problem S
6 Canteen E-Mail Problem P
Where P=Pending Complaints
S=Solved Complaints
Now, I want the result like this.
Comp_dept Problem_type Total Comp Pending Solved
IT Mouse Problem 2 1 1
HR Keyboard Problem 1 1 0
HR Monitor Problem 1 0 1
Accounts Network Problem 1 1 0
Canteen E-Mail Problem 1 1 0
Please help anyone its Important.
Thanks.
|
|
|
|
|
Re: Complex Query [message #326982 is a reply to message #326975] |
Fri, 13 June 2008 05:13   |
sarwagya
Messages: 87 Registered: February 2008 Location: Republic of Nepal
|
Member |
|
|
WITH DATA AS (SELECT 1 COMP_NO,'IT' COMP_DEPT,'MOUSE PROBLEM' PROB_TYPE, 'P' COMP_STATUS FROM DUAL
UNION ALL
SELECT 2 COMP_NO,'HR' COMP_DEPT,'KEYBOARD PROBLEM' PROB_TYPE, 'P' COMP_STATUS FROM DUAL
UNION ALL
SELECT 3 COMP_NO,'IT' COMP_DEPT,'MOUSE PROBLEM' PROB_TYPE, 'S' COMP_STATUS FROM DUAL
UNION ALL
SELECT 4 COMP_NO,'ACCOUNTS' COMP_DEPT,'NETWORK PROBLEM' PROB_TYPE, 'P' COMP_STATUS FROM DUAL
UNION ALL
SELECT 5 COMP_NO,'HR' COMP_DEPT,'MONITOR PROBLEM' PROB_TYPE, 'S' COMP_STATUS FROM DUAL
UNION ALL
SELECT 6 COMP_NO,'CANTEEN' COMP_DEPT,'E-MAIL PROBLEM' PROB_TYPE, 'P' COMP_STATUS FROM DUAL
)
SELECT COMP_DEPT,PROB_TYPE,COUNT(*) TOTAL,SUM(DECODE(COMP_STATUS,'P',1,0)) PENDING,SUM(DECODE(COMP_STATUS,'S',1,0)) SOLVED
FROM DATA
GROUP BY COMP_DEPT,PROB_TYPE
ORDER BY TOTAL DESC
|
|
|
|
|
|
|