Aggregate Function question
Hi,
is it possible to do the following query?
select a.x, a.y-sum(b.z) from a,b where a.ix=b.ix
I know there is a 'group by' missing, but by what shall I group it?
According to the syntax guide the above is possible, but it comes up
with an error message saying group by missing.
Any help appreciated.
The work-araound is of course using a view or a third table, which
already has the sums.
Regards,
Kai Shen
Acer Computer Germany
DBA
I.T.Europe
Kai Shen wrote:
>
> Hi,
>
> is it possible to do the following query?
>
> select a.x, a.y-sum(b.z) from a,b where a.ix=b.ix>
> I know there is a 'group by' missing, but by what shall I group it?
> According to the syntax guide the above is possible, but it comes up
> with an error message saying group by missing.
>
> Any help appreciated.
>
> The work-araound is of course using a view or a third table, which
> already has the sums.
To do this you need a sub-query:
select a.x, a.y - (select sum(b.x) from b where a.ix = b.ix)
from a;
Art S. Kagel
I believe you have to use an alias for the column. In other words: select a.x Value1, a.y-sum(b.z) Value2 from a,b where a.ix=b.ix group by Value1, Value2 Try this and let me know if it works out.
Kai Shen wrote:
> select a.x, a.y-sum(b.z) from a,b where a.ix=b.ix>
> I know there is a 'group by' missing, but by what shall I group it?
> According to the syntax guide the above is possible, but it comes up
> with an error message saying group by missing.
I think what you want is:
SELECT a.x, a.y - SUM(b.z)
FROM a, b
WHERE a.ix = b.ix
GROUP BY a.x, a.y
This ought to work.
June
--
june_t@hotmail.com
Still alive (barely), recently escaped from Harrisburg, PA
Please do not send Informix questions to this account.
I would add 'Please do not send spam to this account'
but I suppose I would be wasting my bits.