SQL Group By - counting records per month/year, error on insert - NOT A VALID MONTH

Question

I have this example data:

Country | Members   | Joined
USA     | 250       | 1/1/2012
USA     | 100       | 1/8/2012
Russia  | 75        | 1/20/2012
USA     | 150       | 2/10/2012

When I query this data I would like to aggregate all the records in a given month. The result of the query would look like:

Country | Members   | Joined
USA     | 350       | 1/2012
Russia  | 75        | 1/2012
USA     | 150       | 2/2012

As a select that is simple enough:

select country, count(*) as members , to_char(trunc(joined), 'MM-YYYY')
from table
group by country, to_char(trunc(joined), 'MM-YYYY')

That query will give me data in the format I want, however my issue is that when I go to insert that into a new pivot table I get an error because the to_char() in the select statement is being placed into a DATETIME column (error: ORA-01843 - not a valid month)

When I change the to_char() in the select to to_date() , it still doesn't work (same error, ORA-01843 - not a valid month):

select country, count(*) as members, to_date(trunc(joined), 'MM-YYYY')
from table
group by country, to_date(trunc(joined), 'MM-YYYY')

Any suggestions on how to modify this query in such a way that I can insert the result into a new table whose "JOINED" column is of type DATETIME?

thanks in advance for any tips/suggestions/comments!

Solution 1

You can do something like to_date('01/'||trunc(joined), 'DD/MM/YYYY'), which would turn it into a valid date first. You just need to decide whether to use the first or last day of the month (last is more complicated)

Another option is to use the EXTRACT function:

 select country, count(*) as members, EXTRACT(MONTH FROM joined) as mn, EXTRACT(YEAR FROM JOINED) as yr,MIN(JOINED) as dt
from table
group by country, EXTRACT(MONTH FROM joined), EXTRACT(YEAR FROM JOINED)

and then from that, you could just select the dt column and insert it

Solution 2

You should be using the trunc function to truncate the date to the first of the month. That eliminates the conversion of the date to a string and the need to convert the string back to a date.

select country, 
       count(*) as members , 
       trunc(joined, 'MM')
  from table
 group by country,
          trunc(joined, 'MM')

Comments

[Pat Grady]

in my head this was exactly what i was trying to figure out. thanks much!

[Pat Grady]

good suggestion, i'll look into this. i appreciate your feedback