SQL Anywhere database - I have a situation to sum the rows

Author

I have a situation to sum the rows

Karthik

2005-12-22, 9:23 am

Hi All,

I use Sybase ASA function in order to this.

I have a complex sql that has result set as follows

corp_code loc_code workers_comp_amount
2 1 $120.00
2 2 $240.00
2 3 $140.00

All I need is what is the easy way to loop through the records and sum the workers_comp_amount without using the cursor.

RIght now I am using Cursor in order define the sql and sum the sql inside the cursor loop.

Any help will be greatly appreciated

Thanks
Karthik

Glenn Paulley

2005-12-22, 9:23 am

Put your complex SQL into a derived table, and use the SUM() aggregate
function to sum the workers_comp_amount from each of the input rows:

Select SUM( workers_comp_amount)

From (
Select corp_code, loc_code, workers_comp_amount
From ....

....
complex SQL statement
....
) as DT( corp_code, loc_code, workers_comp_amount )

Glenn

"Karthik" <kpanc@laborfinders.com> wrote in
news:43aab4dd$1@foru
ms-2-dub:

> Hi All,
>
> I use Sybase ASA function in order to this.
>
> I have a complex sql that has result set as follows
>
> corp_code loc_code
> workers_comp_amount 2 1
> $120.00 2 2
> $240.00 2 3
> $140.00
>
>
> All I need is what is the easy way to loop through the
> records and sum the workers_comp_amount without using the
> cursor.
>
> RIght now I am using Cursor in order define the sql and
> sum the sql inside the cursor loop.
>
> Any help will be greatly appreciated
>
> Thanks
> Karthik
>
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Pavel Karady

2005-12-22, 11:23 am

Karthik, you haven't specified your ASA version. I believe, Glenn's
solution, which is using SELECT in FROM clause,
which is not supported in ASA 7 (I don't know if it's from ASA 8 or ASA 9).

I recommend what Glenn recommended, but with filling a separate temporary
table with your complex statement. Then you can SUM freely.

Pavel

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns973464899D14
4paulleyianywherecom
@10.22.241.106...
> Put your complex SQL into a derived table, and use the SUM() aggregate
> function to sum the workers_comp_amount from each of the input rows:
>
> Select SUM( workers_comp_amount)

> From (
> Select corp_code, loc_code, workers_comp_amount
> From ....
>
> ....
> complex SQL statement
> ....
> ) as DT( corp_code, loc_code, workers_comp_amount )
>
> Glenn
>
>
>
> "Karthik" <kpanc@laborfinders.com> wrote in
> news:43aab4dd$1@foru
ms-2-dub:
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Karthik

2005-12-22, 11:23 am

Sorry for not mentioning the version,
Right now we are using ASA 8

But all this changes I am talking is for ASA 9

Thanks
Karthik

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43aac825$1@foru
ms-2-dub...
> Karthik, you haven't specified your ASA version. I believe, Glenn's
> solution, which is using SELECT in FROM clause,
> which is not supported in ASA 7 (I don't know if it's from ASA 8 or ASA
> 9).
>
> I recommend what Glenn recommended, but with filling a separate temporary
> table with your complex statement. Then you can SUM freely.
>
> Pavel
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns973464899D14
4paulleyianywherecom
@10.22.241.106...
>
>

Dmitri

2005-12-22, 11:23 am

Pavel Karady wrote:

> Karthik, you haven't specified your ASA version. I believe, Glenn's
> solution, which is using SELECT in FROM clause,
> which is not supported in ASA 7

It worked in 5.5 - why should it stop in 7 ;) ?

> I recommend what Glenn recommended, but with filling a separate temporary
> table with your complex statement. Then you can SUM freely.

Would work, but isn't really needed here.

Dmitri.

Sponsored Links