Home » SQL & PL/SQL » SQL & PL/SQL » Date
Date [message #296500] Mon, 28 January 2008 00:29 Go to next message
ramyashree.rp
Messages: 9
Registered: January 2007
Junior Member
Hi,
can anybody please help me how to print the count of records month wise?

ex:
Month Count
----- -----
Jan 2007 5
Jan 2008 6
Re: Date [message #296505 is a reply to message #296500] Mon, 28 January 2008 00:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

use GROUP BY, COUNT() and TO_CHAR(date_col,'MONYYYY')

regards,
Re: Date [message #296513 is a reply to message #296500] Mon, 28 January 2008 00:57 Go to previous messageGo to next message
ramyashree.rp
Messages: 9
Registered: January 2007
Junior Member
i have used this but i am getting different output.

ex:
month count
----- -----
jan2007 1
jan2007 1
....

but i want the month to print only once and the total count.
Re: Date [message #296516 is a reply to message #296513] Mon, 28 January 2008 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us the query.
Do a copy from your sql-plus session and paste it here.
Surround it (in your post) with [code] and [/code] tags to make it more readable.
Re: Date [message #296549 is a reply to message #296500] Mon, 28 January 2008 02:49 Go to previous messageGo to next message
ramyashree.rp
Messages: 9
Registered: January 2007
Junior Member
select to_char(date_created,'mon') "month",count(emp_id)from emp 
where to_char(date_created,'Mon') = 'Jan'
group by to_char(date_created,'mon'),emp_id
Re: Date [message #296551 is a reply to message #296549] Mon, 28 January 2008 02:52 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Don't group by empid.
You might want to have a look at the documentation and do a bit of research.
this site contains the documentation that you will need, check out the SQL reference
Re: Date [message #296552 is a reply to message #296549] Mon, 28 January 2008 02:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you group by to_char(date_created,'mon'),emp_id you will get a row for each combination of the two.

[Edit: slow again...]

[Updated on: Mon, 28 January 2008 02:54]

Report message to a moderator

Re: Date [message #296553 is a reply to message #296549] Mon, 28 January 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you group by "to_char(date_created,'mon')" you will mix the different years (each year has a "jan" month).
group by TRUNC(date_created,'month')

Regards
Michel
Re: Date [message #296556 is a reply to message #296500] Mon, 28 January 2008 03:10 Go to previous message
ramyashree.rp
Messages: 9
Registered: January 2007
Junior Member
thanks for all who have replied. it solved my problem.
Previous Topic: Error consuming https web services
Next Topic: Oracle query
Goto Forum:
  


Current Time: Mon Dec 05 21:33:00 CST 2016

Total time taken to generate the page: 0.12304 seconds