Home » SQL & PL/SQL » SQL & PL/SQL » Complex Query (Oracle)
icon4.gif  Complex Query [message #326975] Fri, 13 June 2008 04:49 Go to next message
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 #326976 is a reply to message #326975] Fri, 13 June 2008 04:54 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Can you please send the CREATE TABLE AND INSERT STATEMENTS?
Re: Complex Query [message #326981 is a reply to message #326975] Fri, 13 June 2008 05:12 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Study pivot query??

Hint--

Max(decode(

Regards,
Rajat
Re: Complex Query [message #326982 is a reply to message #326975] Fri, 13 June 2008 05:13 Go to previous messageGo to next message
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
Re: Complex Query [message #326983 is a reply to message #326975] Fri, 13 June 2008 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You neet to investigate the concept of SUM(DECODE(field,'Value 1',1,0)), and GROUP BY

[Too Slow!!!]

[Updated on: Fri, 13 June 2008 05:15]

Report message to a moderator

Re: Complex Query [message #327071 is a reply to message #326975] Fri, 13 June 2008 11:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
JR, what you mean "TOO SLOW"? What is too slow, CONDITIONAL SUMMATION (eg. sum(decode...))?, or ROWSOUCE CONSTRUCTION (eg. union all from dual)?

Can you explain a little more please because I use these all the time? If you have something better I am all ears.

Kevin
Re: Complex Query [message #327078 is a reply to message #327071] Fri, 13 June 2008 12:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin, I think he meant "too slow" as in he didn't get his answer in before the same response was given Smile
Re: Complex Query [message #327080 is a reply to message #326975] Fri, 13 June 2008 12:29 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Laughing DOH! how silly of me. Thanks Joy, Kevin
Previous Topic: Crosstab query help needed
Next Topic: Query to display prime numbers
Goto Forum:
  


Current Time: Fri Dec 02 18:30:01 CST 2016

Total time taken to generate the page: 0.18958 seconds