Home » Developer & Programmer » Reports & Discoverer » A new SQL Help Request (10g)
A new SQL Help Request [message #512861] Wed, 22 June 2011 10:19 Go to next message
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





Re: A new SQL Help Request [message #512866 is a reply to message #512861] Wed, 22 June 2011 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: A new SQL Help Request [message #512867 is a reply to message #512861] Wed, 22 June 2011 10:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to group by to_char(ocp.complete_date,'FMMonth YYYY') rather than complete_date.
Re: A new SQL Help Request [message #512878 is a reply to message #512867] Wed, 22 June 2011 11:46 Go to previous messageGo to next message
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
Re: A new SQL Help Request [message #512879 is a reply to message #512878] Wed, 22 June 2011 11:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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> 
Re: A new SQL Help Request [message #512885 is a reply to message #512879] Wed, 22 June 2011 13:01 Go to previous messageGo to next message
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!!
Re: A new SQL Help Request [message #512893 is a reply to message #512885] Wed, 22 June 2011 13:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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

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


Current Time: Fri Mar 29 07:31:52 CDT 2024