Home » SQL & PL/SQL » SQL & PL/SQL » Regarding formatting using pivot Queries
Regarding formatting using pivot Queries [message #328418] Fri, 20 June 2008 02:20 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,
i have three queries below

select itemtype, count(itemtype) A_type
from EFORMDYNAMICFIELDINSTANCE
where ownerid=60129
and cm_creationdate 
between to_date('01-MAR-2008','dd-mm-yyyy')
and to_date('11-JUNE-2008','dd-mm-yyyy')
and CM_OVERALLSTATUS ='Closed'
group by itemtype

select itemtype,count(itemtype) B_type
from EFORMDYNAMICFIELDINSTANCE
where ownerid=60129
and cm_creationdate < to_date('01-MAR-2008','dd-mm-yyyy')
and CM_OVERALLSTATUS ='Closed'
group by itemtype

select itemtype,count(itemtype) C_type
from EFORMDYNAMICFIELDINSTANCE
where ownerid=60129
and cm_creationdate 
between to_date('01-MAR-2008','dd-mm-yyyy')
and to_date('11-JUNE-2008','dd-mm-yyyy')
and CM_OVERALLSTATUS ='Open'
group by itemtype


i want to combine them into one and display in the following format

ITemType A_type B_Type C_type
X        3      4      5
Y        3      10     11


are pivot queries the answer to this problem, or is there another option.
please let me know.
Re: Regarding formatting using pivot Queries [message #328432 is a reply to message #328418] Fri, 20 June 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
are pivot queries the answer to this problem

Yes.

Regards
Michel
Re: Regarding formatting using pivot Queries [message #328543 is a reply to message #328432] Fri, 20 June 2008 07:52 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
thank you Michel,

i have created the following Query

SELECT itemtype, A, B, C
FROM (SELECT itemtype, 
	     max(case when ownerid=60129
		           and cm_creationdate between to_date('01-MAR-2008','dd-mm-yyyy') 
                           and to_date('11-JUNE-2008','dd-mm-yyyy')
		           and CM_OVERALLSTATUS ='Closed'
		       then count(itemtype) else null END) A, 	   
	     max(case when ownerid=60129
                           and cm_creationdate < to_date('01-MAR-2008','dd-mm-yyyy')
                           and CM_OVERALLSTATUS ='Closed'
		      then count(itemtype) else null END) B,
             max(case when ownerid=60129
                           and cm_creationdate between to_date('01-MAR-2008','dd-mm-yyyy')
                           and to_date('11-JUNE-2008','dd-mm-yyyy')
                           and CM_OVERALLSTATUS ='Open'
		      then count(itemtype) else null END) C
	 FROM EFORMDYNAMICFIELDINSTANCE
	 GROUP BY itemtype					   
	 );



it is giving the error

not a single-group group funtion


i believe this is because of the Count() finc that i am using.
please let me know where should i use the group by clause since it is essential for Count() func

[Updated on: Fri, 20 June 2008 07:57]

Report message to a moderator

Re: Regarding formatting using pivot Queries [message #328553 is a reply to message #328543] Fri, 20 June 2008 08:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
case expression must be put inside the count and not outside.

Regards
Michel
Previous Topic: Date format
Next Topic: How to convert Oracle datas to MS Access file
Goto Forum:
  


Current Time: Sun Dec 04 06:26:36 CST 2016

Total time taken to generate the page: 0.08935 seconds