how to code sql query if need to sum only first 50 rows

sandhya rani · New User Joined: 05 Dec 2006 Posts: 22 Location: hyderabad

Hi,

Please let me know how do i sum the amount for first 50 retrieved rows.

if my query results in 100(more then 50) but i need to sum the amount of only first 50 rows fetched.Please suggest accordingly.

My query is related to mainframe technogy in Cobol-db2 program

guptae · Posted: Wed Dec 06, 2006 10:15 am

Hi there,

R ur program already have a cursor on the table for some processing?

mubashir.surury · New User Joined: 29 Sep 2005 Posts: 23 Location: Pune

Hi Sandhya,

You can write your query to get the SUm of the required rows and at the end of the just give 'FETCH FIRST 50 ROWS ONLY'.if there is some sort criteria for the same query you might want order you rowset as per that one.

Eg......

EXEC SQL
SELECT <your columns you need to fetch>
FROM <TABLE(S)>
WHERE <your selection criteria>
ORDER BY <approp. columns>
FETCH FIRST 50 ROWS ONLY
END SQL

Correct me if I am wrong

Regards,
Mubashir

rambabu · New User Joined: 18 Apr 2005 Posts: 67

hi,

SELECT <your columns you need to fetch> ,sum (amount)
FROM <TABLE(S)>
FETCH FIRST 50 ROWS ONLY

guptae · Posted: Wed Dec 06, 2006 3:13 pm

Hi There,

Following query

mubashir.surury · New User Joined: 29 Sep 2005 Posts: 23 Location: Pune

Yes Ekta,

I agree But the point here is to sum up the first 50 rows.........But any way yes we need to use GROUP BY Clause while using a COLUMN FUNCTION such as SUM.........

Regards,
Mubashir

sandhya rani · New User Joined: 05 Dec 2006 Posts: 22 Location: hyderabad

HI mubashir,

Ya actually i tried the same query without using order by and it has fetched only 50 rows.

inorder to sum up the amount i have given the query as follows

EXEC SQL
SELECT sum(amount-field-name)
FROM <TABLE(S)>
WHERE <your selection criteria>
FETCH FIRST 50 ROWS ONLY
END SQL

but this query gave the sum of all the rows retrieved but it has not given the sum of only first 50 rows.

As per my understanding fetch only works on fetchings of the rows but does not work on functions like SUmming etc...so i just wanted to know if there is any easier query which gives solution for my query.

anyways iam able to achieve this using cursors but still if there is any easier methos i jus wanted to know.Please let me know if u have any updates on the same.Thanks

Thanks & Regards
Sandhya Rani

sandhya rani · New User Joined: 05 Dec 2006 Posts: 22 Location: hyderabad

Hi ,

I have posted the reply please look into that and let me know if you have any updates on the same.I have already tried the same but still it was summing the amount for all the retrieved rows it is not restricting to 50 rows.

Thanks & regards
Sandhya Rani

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

This is not a straight forward way but consider this:

Create Global temporary table, insert 50 rows you want SUM to be done on, then use SUM on temp. table.

Alan Voss · Posted: Wed Dec 06, 2006 10:52 pm

How about:
select sum(a.amount) as total
from (select amount
from table1
fetch first 50 rows only) a;

sandhya rani · New User Joined: 05 Dec 2006 Posts: 22 Location: hyderabad

Hi,

I have tried the query as follows

select sum(a.amount) as total
from (select amount
from table1
where fields1 = '12345'
fetch first 50 rows only)

but it is giving error as
QUERY MESSAGES:
SQL error at or before FETCH (line 6, position 9). ---i think it does not accept fetch...

please correct me if there is anything wrong in the query that i tried.

Thanks & regards
Sandhya Rani

Alan Voss · Posted: Thu Dec 07, 2006 6:34 pm

I just tried it again and it worked for me. Then, I tried it on z/OS DB2 and I got a message similar (but different) from yours. Apparently, that is one different between DB2 on AIX and DB2 on z/OS.

Sorry, but it doesn't look like I can help you.

DavidatK · Posted: Wed Dec 27, 2006 7:11 am

Sandhya Rani,

Did you ever get a resolution to your question?

Does this have to be in a single SQL?

What version of DB2 are you using. V8 and V9 appear to have some enhancements in this area.

sandhya rani · New User Joined: 05 Dec 2006 Posts: 22 Location: hyderabad

Hi David,

I have not yet got the solution meanwhile iam procedding using cursors for the same.But iam also searching for a better solution.Please do share with me if you have any information regarding the same.BD2 version that we are using are as follows

DB2 Version: 7.1
FADB2 Version: 4.8.1.13
SQLP Version: 04.08.01
FM Version: R2.1

Thanks & regards
Sandhya Rani