Group-by with calculated date gives "non-group-by-expre

Posted by tosa1 on 07-Dec-2012 03:23

Hi,

I'm having trouble getting a group-by query working. First I had this working bit of SQL:

Code:
select year(date1+numberofdays), week(date1+numberofdays), sum(qty) 
from pub.table 
group by year(date1+numberofdays), week(date1+numberofdays)
(date1 is a date and numberofdays an integer.)

The problem is that where I'm from, the week starts on Monday, but Openedge apparently uses the North American standard where it starts with Sunday. So I thought I could just subtract the date with 1 day, but then I get a "non-group-by expression in select clause" error, with the SQL looking like this:

Code:
select year(date1+numberofdays), week(date1+numberofdays-1), sum(qty) 
from pub.table 
group by year(date1+numberofdays), week(date1+numberofdays-1)
However, it works if I select the subtracted date without the aggregate function, like this:

Code:
select year(date1+numberofdays), week(date1+numberofdays-1), qty from pub.table
What could be the problem? It's a Progress 10.1A database.

Thank you!

OpenEdge RDBMS - Forum

All Replies

Posted by Jens Dahlin on 07-Dec-2012 06:38

Is that your exact code? Could week perhaps be an internal function that you're calling by mistake. The progress built in function for getting day of week (with 1 being sunday) is weekday(date) and not week(date)?

Posted by tosa1 on 07-Dec-2012 07:15

Hi,

I'm using the ODBC interface, forgot to mention that. I'm trying to get week of year, not day of week, so week() should be the correct function to use.

The query is a bit simplified.

This thread is closed