COMPUTE FUNCTION EQUIVALENT
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 typeCOMPUTE SUM(price), SUM(advance) BY type
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 !!
How about:
SELECT type, SUM(price), SUM (advance)
FROM titles
WHERE type like '%cook'
ORDER BY type
GROUP BY type
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
>
> 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 !!
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 !!
>