GROUP BY alias...possible?

Hello,
I have a statement as follows:
SELECT field1, field2, {expression} as calc1
FROM table1
GROUP BY field1, calc1
The absolutely critical thing to this statement is that I need to group
by calc1, but I get an 'Invalid Column Name' error. However, to get the
results I need I have to do a calculation as the data required is not
available in that form in the table, and I also have to be able to
group by it too. I'm sure someone must have come across this problem
and found a workaround?Well, one way to do this is to use calculation in the group by:
select
col1-col2 as Diff,
col3
from
table1
group by
(col1-col2),
col3
Is this good enough or you really need to use the same name?
MC
<champ.supernova@.gmail.com> wrote in message
news:1133343294.033508.241990@.o13g2000cwo.googlegroups.com...
> Hello,
> I have a statement as follows:
> SELECT field1, field2, {expression} as calc1
> FROM table1
> GROUP BY field1, calc1
> The absolutely critical thing to this statement is that I need to group
> by calc1, but I get an 'Invalid Column Name' error. However, to get the
> results I need I have to do a calculation as the data required is not
> available in that form in the table, and I also have to be able to
> group by it too. I'm sure someone must have come across this problem
> and found a workaround?
>|||champ.supernova@.gmail.com wrote on 30 Nov 2005 01:34:54 -0800:

> Hello,
> I have a statement as follows:
> SELECT field1, field2, {expression} as calc1
> FROM table1
> GROUP BY field1, calc1
> The absolutely critical thing to this statement is that I need to group
> by calc1, but I get an 'Invalid Column Name' error. However, to get the
> results I need I have to do a calculation as the data required is not
> available in that form in the table, and I also have to be able to
> group by it too. I'm sure someone must have come across this problem
> and found a workaround?
You can use {expression} in the group by instead of calc1 (just copy the
expression from the select), or use a subquery, or use DISTINCT. However,
you've still got a problem - you can't have field2 in your select list if
you're not grouping by it. If you don't need that field, remove it from the
select, if you do then add it to your group by. Assuming that you don't want
it, here's a couple of ideas:
SELECT field1, {expression} as calc1
FROM table1
GROUP BY field1, {expression}
SELECT DISTINCT field1, {expression} as calc1
FROM table1
SELECT field1, calc1
FROM
(SELECT field1, {expression} as calc1
FROM table1) As A
GROUP BY field1, calc1
SELECT DISTINCT field1, calc1
FROM
(SELECT field1, {expression} as calc1
FROM table1) As A
Dan|||Thanks guys, MC's answer solved it. Much obliged.