On Fri, 15 Nov 2002 07:12:24 +1100 Steve Baldwin <[EMAIL PROTECTED]> wrote:
> How about : > > Select to_char(actual_date, 'YYYY-Q'), sum (value) from daily_data > Where sensor_id=? > Group by to_char(actual_date, 'YYYY-Q') > > Couldn't you have the to_char format mask as a bind variable ? You certainly can put the format in a placeholder. It's a VARCHAR value just like any other. > -----Original Message----- > From: Fong, Anna [mailto:anna@;water.ca.gov] > Sent: Friday, 15 November 2002 7:01 AM > To: [EMAIL PROTECTED] > Subject: OT: Oracle Group By statement > > > Can anyone point me to the answer? > > I want to use an arbitrary group in the group by statement. I can group > by year but want to change the calendar year to another unit of measure > such as quarter. > > This works: > > select to_char(actual_date,'YYYY'), sum(value) from daily_data > where sensor_id=? > group by to_char(actual_date,'YYYY') You can group any way you like. If you want some granularity not supported by a format value, select a finer format and combine the parts in Perl. For example use 'YYYY-MM' and combine pairs of months if you want two month granularity. You could even just select everything and combine them in a hash in Perl. What works best depends on the table size and how much of it you have to fetch by the time you are done. For example (not tested): # Get all sensor data at once $dbh -> {"RaiseError"} = 1; # _ALWAYS_ check for errors my $sth = $dbh -> prepare( <<SQL ); SELECT sensor_id, TO_CHAR( actual_date, 'YYYYMMDD' ), SUM( value ) FROM daily_data GROUP BY sensor_id, TO_CHAR( actual_date, 'YYYYMMDD' ) SQL $sth -> execute(); my ( $id, $date, $value, %daily_data ); $sth -> bind_columns( \( $id, $date, $value ) ); while ( $sth -> fetch ) { # Combine day pairs $daily_data{$id}{int(($date-1)/2) * 2 + 1 } += $value; } # Do something with days by ID -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
