Re: COMPUTE FUNCTION EQUIVALENT
once again, you have shown me the light!
thanks!
Art S. Kagel wrote:
> tomcaml@yahoo.com wrote:
> > hello,
> > i am looking to sum a summed column. in sql server, there is a
compute
> > function that will perform basically a sum of a sum. is there any
way
> > to do this through sql in informix? we are on 9.40.
> >
> > MSSQL SQL:
> >
> > SELECT type, price, advance
> > FROM titles
> > WHERE type LIKE '%cook'
> > ORDER BY type> > COMPUTE SUM(price), SUM(advance) BY type
>
> This is of course related to your feature request. The reason
Informix and
> other servers never included this non-standard SQL extension which
SQL
> Server inherited from Sybase is that it can be done in standard SQL.
So,
> try it this way:
>
> SELECT ' ' level, type, price, advance
> FROM titles
> WHERE type LIKE '%cook'
> UNION ALL
> SELECT 'total' level, type, SUM(price), SUM(advance)
> FROM titles
> WHERE type LIKE '%cook'
> GROUP BY TYPE
> ORDER BY 2, 1;
>
> Output:
>
>
> level type price advance
>
> mod_cook 19.9900000000000 0.00000000000000
> mod_cook 2.99000000000000 15000.0000000000
> total mod_cook 22.9800000000000 15000.0000000000
>
> 3 row(s) retrieved.
>
> Beyond that you can accomplish the same thing and/or clean up this
output
> when you are processing the output in a host language like ISQL,
I4GL,
> ESQL/C, Java, etc. The COMPUTE feature is really redundant, in my
opinion.
>
> Art S. Kagel
>
>
> > here is an example of data:
> >
> > type price advance
> > ------------ -------------------------- --------------------------
> > mod_cook 19.99 0.00
> > mod_cook 2.99 15,000.00
> >
> > sum
> > ==========================
> > 22.98
> > sum
> > ==========================
> > 15,000.00
> >
> >
> >
> > thanks !!
> >