Home » SQL & PL/SQL » SQL & PL/SQL » To display count for each day in a month (10g)
To display count for each day in a month [message #604947] Mon, 06 January 2014 07:02 Go to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
HI,


I have a table audits

select * from audits;

date module application
------ ------- ------------
01-JAN-2014 X abc
01-JAN-2014 X abc
02-JAN-2014 Y cde
03-JAN-2014 X ghY
..........


I need a query like to display output as below. i mean the count for each day in a month
Please help me.

Module application 01-JAN-2013 02-jan-2013 03-jan-2013
------ ---------- ---------- ------------ -------------
X abc 2 0 0
X ghy 0 1 0
Y cde 0 0 1
Re: To display count for each day in a month [message #604948 is a reply to message #604947] Mon, 06 January 2014 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Standard pivot query, search for this word.

Re: To display count for each day in a month [message #604952 is a reply to message #604948] Mon, 06 January 2014 08:24 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member

I tried with PIVOT but not able to get output.
i am not knowing how to use decode function for audit_date to get count and again use it in pivot
Could u please write the query once.
Re: To display count for each day in a month [message #604953 is a reply to message #604952] Mon, 06 January 2014 08:34 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
select MODULE,
APPlication,
to_number(to_char(last_day(to_date(sysdate,'dd-mm-yyyy')),'dd')) ,
X.*
from ( select MODULE,APPlication, to_char( audit_date, 'dd' ) day, audit_date
from audit_1
where audit_date between to_char(trunc (sysdate, 'month'),'dd-mon-yyyy')
and TO_CHAR(LAST_DAY(sysdate),'dd-mon-yyyy')
pivot (sum(to_char(audit_date,'dd')) for day in
('01','02', '03','04', '05','06','07','08','09','10',
'11','12','13','14','15','16','17','18','19','20',
'21','22','23','24','25','26','27', '28','29','30','31')
) X
;


More over i need to get columns in date format like 01-jan-2014 02-jan-2014 ........
i need to get these dates dynamically
but with the above i am getting 01 02 03......

Please help me

[Updated on: Mon, 06 January 2014 08:53]

Report message to a moderator

Re: To display count for each day in a month [message #604954 is a reply to message #604953] Mon, 06 January 2014 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PIVOT form does not exist in 10g, yo have to use the old DECODE way.

Re: To display count for each day in a month [message #604956 is a reply to message #604954] Mon, 06 January 2014 09:19 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
Actually it has to be dynamic....for feb it will be having 28 days, then how can i decode it dynamically


In 11g how we will use Pivot for this to get dynamically

[Updated on: Mon, 06 January 2014 09:20]

Report message to a moderator

Re: To display count for each day in a month [message #604957 is a reply to message #604956] Mon, 06 January 2014 09:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It can't be dynamic neither in 10g nor in 11g.

Re: To display count for each day in a month [message #604959 is a reply to message #604953] Mon, 06 January 2014 09:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
367364 wrote on Mon, 06 January 2014 09:34
More over i need to get columns in date format like 01-jan-2014 02-jan-2014 ........
i need to get these dates dynamically


And what if you have data for several months/years? Keep in mind, table can have no more than 1000 columns. And, in any case, you can't do it dynamically. Usually "dynamic" pivot is used in reporting and most reporting tools have that capability. Are you using SQL*Plus? If so, you could use SQL*Plus substitution variables to construct and execute pivot statement.

SY.
Re: To display count for each day in a month [message #605026 is a reply to message #604959] Tue, 07 January 2014 00:10 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
Please help me how to do using substitution variables

Or is there is any other way to generate it dynamically ..i mean using a procedure or function.
Re: To display count for each day in a month [message #605027 is a reply to message #605026] Tue, 07 January 2014 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes you can do it using a procedure that returns a ref cursor.
Barbara Boehmer posted such code here several times, please do search.

Re: To display count for each day in a month [message #605031 is a reply to message #605027] Tue, 07 January 2014 01:12 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 272
Registered: August 2002
Senior Member
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:7467219000346297165

check this hope this will suits you better.
Re: To display count for each day in a month [message #605032 is a reply to message #605031] Tue, 07 January 2014 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it does not, this solution has been suggested above and rejected as it is not dynamic.

Re: To display count for each day in a month [message #605035 is a reply to message #605032] Tue, 07 January 2014 01:55 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member

I searched for Barbara Boehmer posts but i didn't find any solution for this.
Please help me
Re: To display count for each day in a month [message #605038 is a reply to message #605035] Tue, 07 January 2014 02:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2215
Registered: May 2013
Location: World Wide on the Web
Senior Member
Search with keyword "Pivot" and filter with user "Barbara Boehmer".

http://www.orafaq.com/forum/?SQ=92de0d45f9b46ae22c18342d3a31bbf8&t=search&srch=pivot&btn_submit=Search&field=all& forum_limiter=&attach=0&search_logic=AND&sort_order=DESC&author=barbara+boehmer
Re: To display count for each day in a month [message #605041 is a reply to message #605038] Tue, 07 January 2014 02:14 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
It doesn't have solution for dynamically populating of dates.
Re: To display count for each day in a month [message #605042 is a reply to message #605041] Tue, 07 January 2014 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't read the topics.

Re: To display count for each day in a month [message #605043 is a reply to message #605041] Tue, 07 January 2014 02:29 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
367364 wrote on Tue, 07 January 2014 08:14
It doesn't have solution for dynamically populating of dates.

You realise that there were dozens of topics there? I will guarantee you that several of them contain the information that you need.
Re: To display count for each day in a month [message #605133 is a reply to message #605043] Tue, 07 January 2014 09:03 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
I created a function using ref cursor as below

create or replace function pivot_func
return SYS_REFCURSOR as
  v_sql	 long;
  v_refcur  sys_refcursor;
  v_firstdate date;
  v_days  number;
begin

select to_date(to_char(trunc (sysdate, 'month'),'dd-mon-yyyy'))
    into v_firstdate
    from dual;
	
  V_DAYS := TRUNC(TO_CHAR(SYSDATE,'DD')); 
      v_sql :=
    	 ' select *
		   from (select module_name,
		                app_name, 
						audit_date
          from audit_trail
         where audit_date between  to_char(trunc (sysdate, '||''''||'month'||''''||'),'||''''||'dd-mon-yyyy'||''''||') 
		       and  TO_CHAR(LAST_DAY(sysdate),'||''''||'dd-mon-yyyy'||''''||'))
   	  pivot  (sum(to_char(audit_date,'||''''||'dd-mm-yy'||''''||')) for audit_date in  (';
   	for i in 1..v_days
    LOOP
     v_sql := v_sql ||''''||v_firstdate ||''''||',';
     V_FIRSTDATE := V_FIRSTDATE + 1; 
    End Loop ;
    V_SQL := RTRIM (V_SQL, ',') || ')) '; 
    dbms_output.put_line(V_SQL);
    open v_refcur for v_sql;
     Return V_Refcur;
     close v_refcur;
   end pivot_func;


i will get the sql as 

  select *
		   from (select module_name,
		                app_name, 
						audit_date
                  from METRICS_AUDIT_TRAIL1
             where audit_date between  to_char(trunc (sysdate, 'month'),'dd-mon-yyyy') 
		       and  TO_CHAR(LAST_DAY(sysdate),'dd-mon-yyyy'))
   	  pivot  (sum(to_char(audit_date,'dd')) for audit_date in  ('01-JAN-14','02-JAN-14','03-JAN-14','04-JAN-14','05-JAN-14','06-JAN-14','07-JAN-14')) 

But my problem is how can i sum up the count of dates here for each day in a month
the query above is giving wrong results

How can i use decode in pivot to get the count of dates for each day



I all the examples they have taken the count of some number....but not with the date

please help me.

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 07 January 2014 09:22]

Report message to a moderator

Re: To display count for each day in a month [message #605140 is a reply to message #605133] Tue, 07 January 2014 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note we have neither your table, nor your data, nor what you actually did and got.
So:
1/ Post a test case
2/ Copy and paste your SQL*Plus session

Re: To display count for each day in a month [message #605431 is a reply to message #605140] Thu, 09 January 2014 06:23 Go to previous messageGo to next message
367364
Messages: 10
Registered: July 2013
Location: Hyderabad
Junior Member
Hi,

i am able to get the count for each day but the issue is i am getting columns in sinqle quotes, how can i replace it.


select *
from (select module_name,
app_name,
audit_date
from audits
where audit_date between to_char(trunc (sysdate, 'month'),'dd-mon-yyyy')
and TO_CHAR(LAST_DAY(sysdate),'dd-mon-yyyy'))
pivot (count(APP_NAME) for audit_date in ('01-JAN-14','02-JAN-14','03-JAN-14','04-JAN-14','05-JAN-14','06-JAN-14','07-JAN-14')) ;


I am getting output as

Module_name '01-jan-14' '02-jan-14' '03-jan-14' '04-jan-14' '05-jan-14'
------------ ----------- ----------- ------------ ----------- ------------
abc 0 2 3 5 6
def 2 3 0 0 0

But i want output as below removing quotes in the columns

Module_name 01-jan-14 02-jan-14 03-jan-14 04-jan-14 05-jan-14
--------- ----------- -------- ------------ ----------- ------------
abc 0 2 3 5 6
def 2 3 0 0 0

Please help me.



[Updated on: Thu, 09 January 2014 06:24]

Report message to a moderator

Re: To display count for each day in a month [message #605442 is a reply to message #605431] Thu, 09 January 2014 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post what is asked.

Re: To display count for each day in a month [message #605474 is a reply to message #605431] Thu, 09 January 2014 12:43 Go to previous message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
367364 wrote on Thu, 09 January 2014 07:23
But i want output as below removing quotes in the columns


Just read documentation - you can alias pivot FOR list values:

for audit_date in ('01-JAN-14' "01-JAN-14",'02-JAN-14' "02-JAN-14",'03-JAN-14' "03-JAN-14",'04-JAN-14' "04-JAN-14",'05-JAN-14' "05-JAN-14",'06-JAN-14' "06-JAN-14",'07-JAN-14' "07-JAN-14")) ;

SY.
Previous Topic: How to read UNCOMMITED data in Oracle?
Next Topic: Loops
Goto Forum:
  


Current Time: Tue Sep 02 19:23:12 CDT 2014

Total time taken to generate the page: 0.12510 seconds