OraFAQ Forum: Reports & Discoverer » A new SQL Help Request

Home » Developer & Programmer » Reports & Discoverer » A new SQL Help Request (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

A new SQL Help Request [message #512861]

Wed, 22 June 2011 10:19

russromei
Messages: 6
Registered: June 2011
Location: Charlotte

Junior Member

I'm trying to display a distinct Month year like 'April 2011'
I'm also trying to count all for Month Year (Like 'April 2011')
So the ouptut would look like (using the data at the bottom)
April 2010 3
May 2010 9
June 2010 4

Note the first line in the select is commented out, was trying it that way.
Any date field can be used to test this

Thanks for any help!

select

--distinct to_char(ocp.complete_date,'FMMonth YYYY') MMYY,
to_char(ocp.complete_date,'FMMonth YYYY') MMYY,
count(distinct to_char(ocp.complete_date,'FMMonth YYYY')) cnt

From offender_class_programs ocp
group by ocp.complete_date

This is the partial result
April 2010 1
April 2010 1
April 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
May 2010 1
June 2010 1
June 2010 1
June 2010 1
June 2010 1

Report message to a moderator

Re: A new SQL Help Request [message #512866 is a reply to message #512861]

Wed, 22 June 2011 10:36

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Fri, 10 June 2011 21:57

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post DDL for tables including relationship between them (PK/FK).

Regards
Michel

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: A new SQL Help Request [message #512867 is a reply to message #512861]

Wed, 22 June 2011 10:38

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need to group by to_char(ocp.complete_date,'FMMonth YYYY') rather than complete_date.

Report message to a moderator

Re: A new SQL Help Request [message #512878 is a reply to message #512867]

Wed, 22 June 2011 11:46

russromei
Messages: 6
Registered: June 2011
Location: Charlotte

Junior Member

No, that didn't work, It is still only counting getting 1 per unique date

Report message to a moderator

Re: A new SQL Help Request [message #512879 is a reply to message #512878]

Wed, 22 June 2011 11:50

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

No idea what you've actually done but it definitely does work:

SQL> WITH DATA AS (SELECT SYSDATE + ROWNUM dat FROM dual CONNECT BY LEVEL < 40)
  2  SELECT to_char(dat,'FMMonth YYYY'), COUNT(*)
  3  FROM DATA
  4  GROUP BY to_char(dat,'FMMonth YYYY');

TO_CHAR(DAT,'FMMONTHYYYY')         COUNT(*)
-------------------------------- ----------
June 2011                                 8
July 2011                                31

SQL>

Report message to a moderator

Re: A new SQL Help Request [message #512885 is a reply to message #512879]

Wed, 22 June 2011 13:01

russromei
Messages: 6
Registered: June 2011
Location: Charlotte

Junior Member

OK working with

select

to_char(ocp.complete_date,'FMMonth YYYY') monthyear, Count(*) cnt

From offender_class_programs ocp

group by to_char(ocp.complete_date,'FMMonth YYYY')

Thanks for you help cookiemonster!!

Report message to a moderator

Re: A new SQL Help Request [message #512893 is a reply to message #512885]

Wed, 22 June 2011 13:48

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Next time, don't forget:

Michel Cadot wrote on Wed, 22 June 2011 17:36

From your previous topic:

Michel Cadot wrote on Fri, 10 June 2011 21:57
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post DDL for tables including relationship between them (PK/FK).

Regards
Michel

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Previous Topic:	SQL HELP
Next Topic:	Oracle Report Generating in Excel

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 06 19:22:50 CST 2025