Invalid Column: Group By with SELECT calc column

WarrenM · #1 (**permalink**) 06-16-04, 09:34

Hi all,

Problem:

Code:

SELECT (FLOOR(( ACTIVITY_DATE - TO_DATE('05-01-04', 'MM-DD-YY') ) /7 )) As WEEKNO, SUM(some_data) FROM some_table WHERE TO_CHAR(ACTIVITY_DATE, 'MM-DD-YY') >= '05-01-2004' GROUP BY WEEKNO

Returns an invalid column name. I'm trying to sum columns based upon which week they fall into. If I take out the SUM and GROUP BY it works; I don't understand why I can't GROUP BY WEEKNO, I have an aggregate function in my select statement?

I've searched the web and can't find any answers and it's been about a day...

Any help would be greatly appreciated!

Thanks
Warren

WarrenM · #2 (**permalink**) 06-16-04, 09:37

Oh, one more thing...

I have already tried moving the Week No calclution into the FROM clause in the form of it's own select statment and I still couldn't group by it after I referenced its alias... just in case someone was thinking of doing that.

carloa · #3 (**permalink**) 06-16-04, 09:55

Code:

SELECT (FLOOR(( ACTIVITY_DATE - TO_DATE('05-01-04', 'MM-DD-YY') ) /7 )) 
FROM ....
WHERE .....
GROUP BY (FLOOR(( ACTIVITY_DATE - TO_DATE('05-01-04', 'MM-DD-YY') ) /7 ))