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.
