db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Derby Function
Date Mon, 27 Feb 2006 19:12:36 GMT
Max Ten wrote:

> Is there any function for - GROUP BY YEAR(), GROUP BY MONTH(), GROUP 
> BY QUARTER() in derby? 

Hi -
There are functions but they are not supported in a GROUP BY clause in 
Derby.  The workaround (a bit kludgy) is to translate the values 
returned by the function to table data values.  I do this by creating a 
table YrMnDy with a column that list all years in my dataset (col Yr) 
and in the column Mn 1-12 and the column Dy 1-31 
then using this query

   select  label, y.Yr, m.Mn, d.Dy, count(*)
  from myData, RptYMD y, RptYMD d, RptYMD m
 where     year(postdate) = y.Yr
    and month(postdate) = m.Mn
     and day(postdate) = d.Dy
group by label, y.Yr , m.Mn, d.Dy

If you don't like aliasing the same table repeatedly or have a lot more 
years to list than 31 you may find it cleaner to use different tables 
for Yr, Mn and Dy.

Hope this helps.

View raw message