Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL Query to sum a variable and divide by another variable.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Thu Jan 19, 2006 8:52 pm    Post subject: SQL Query to sum a variable and divide by another variable.
Reply with quote

Hi,

I need a query to select sum of (variable1) of table1 and divide by (variable2) of table2. Can anyone help me out with the query.

Thanks ,
Siva
Back to top
View user's profile Send private message

pjnithin

Active User


Joined: 22 Dec 2005
Posts: 116

PostPosted: Fri Jan 20, 2006 5:54 am    Post subject:
Reply with quote

A query like this will help you.


SELECT (A.sum/B.num)
FROM
(SELECT SUM(var1) AS sum
FROM table1
WHERE varA = 'give ur value' ) A,
(SELECT var2 as num
FROM table2
WHERE varB = 'give ur value') B

You may have to take the sum in case the varB of table2 is not its primary key. Otherwise make necessary changes so that you'll get only a single value for variable2.

Please let me know if u have any probs.

Thanks,
Nithin.
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 11:30 am    Post subject:
Reply with quote

Hi Siva,

The scope of ur Q shud be explained further a bit. The Sum of all the values of a Column in table1 shud be divided by each value of a Column in table2, is this assumption right? If yes, foll the Qry below.

Select Sum(A.COL)/B.COL from table1 A, table2 B group by B.COL;

Thanks & Regards
Prabs
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 1:34 pm    Post subject:
Reply with quote

Hi ,
Thanks for the response . i have explained little bit more in details, hope this will help..

yes you are right, I have comeup with the following query

SELECT (SUM(A.WEEK1_AMT) / FLOAT(B.RATE)) as total
FROM POPR.table1 A, POPR.table2 B
WHERE A.EMPLOYEE_NO = 63306
AND A.PLAN_NUMBER = 251
AND A.PLAN_NUMBER = B.PLAN_NUMBER
AND A.COVERAGE_MONTH BETWEEN 200401 AND 200410 AND
RATE_EFF_DATE =
(SELECT MAX(B.RATE_EFF_DATE) FROM POPR.table2 B WHERE B.PLAN_NUMBER = 251)
GROUP BY B.RATE
WITH UR;

but the result is coming as

TOTAL
---------
4.385E+02

the result should be as

438.5

which is (SUM(A.WEEK1_AMT)/b.rate

b.rate is 4.9800...and
SUM(A.WEEK1_AMT) is 2183.73

2183.73 / 4.9800 = 438.5

Can anyone help me in getting the exact result...

Thanks in advance!!

Siva
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 2:51 pm    Post subject:
Reply with quote

Hi Siva,

The answer you have got is the right one 4.385E+02. This is nothing but 438.5. or Do u mean to get the value without the Exponential stuff?

Thanks & Regards
Prabs
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 3:30 pm    Post subject:
Reply with quote

Hi Prabs,

Thanks for your reply, yes you are correct..I need to get the value without the Exponential stuff?

Thanks,
siva
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 3:43 pm    Post subject:
Reply with quote

Hi
Just try without that float..

Thanks & Regards
Prabhakaran
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 3:55 pm    Post subject: Re: SQL Query to sum a variable and divide by another variab
Reply with quote

Thanks, If I use with out float its not accepting , It gives me an error
QUERY MESSAGES:
Decimal division is not possible with the values used.

Is there any other function than I can user instead of float to get the value 438.5.

SELECT (SUM(A.WEEK1_AMT) / (B.RATE)) as total
FROM POPR.table1 A, POPR.table2 B
WHERE A.EMPLOYEE_NO = 63306
AND A.PLAN_NUMBER = 251
AND A.PLAN_NUMBER = B.PLAN_NUMBER
AND A.COVERAGE_MONTH BETWEEN 200401 AND 200410 AND
RATE_EFF_DATE =
(SELECT MAX(B.RATE_EFF_DATE) FROM POPR.table2 B WHERE B.PLAN_NUMBER = 251)
GROUP BY B.RATE
WITH UR;


Thanks
Siva
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 4:03 pm    Post subject:
Reply with quote

Hi,

Fine...Lets try this one below..

SELECT Dec(SUM(A.WEEK1_AMT) / (B.RATE),4,1) as total



Thanks & Regards
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 4:36 pm    Post subject:
Reply with quote

Hi,

I'm getting the Same Error message

QUERY MESSAGES:
Decimal division is not possible with the values used.

thanks,
Siva
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 4:39 pm    Post subject:
Reply with quote

Hi,

We have missed the float..

SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE),4,1) as total

We shud be getting this time

Thanks & Regards
Prabs
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 5:12 pm    Post subject:
Reply with quote

Hi Prabhakaran,

Thanks a lot ,

The output looks like

TOTAL
-----------------
438

Is there a way we can bring the decimal value also like

TOTAL
-----------------
438.5

Appreciate your help,

Thanks,
Siva
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 5:34 pm    Post subject:
Reply with quote

Hi,

Try this way
SELECT SUM(A.WEEK1_AMT) / Dec(B.RATE)

(OR)

If that is not working

Just play with that number arg 4 and 1 (try giving 5 and 1)
SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE),5,1)

(OR)

SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE))

By just rearranging the args, we must definitely get it.

Thanks & Regards
Prabs
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Fri Jan 20, 2006 5:49 pm    Post subject:
Reply with quote

Hi,

Thanks a lot....Its working with the second one
SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE),5,1)

I got the answer as

TOTAL
--------
438.5

But one last question, Can you explain about the importance of 5 and 1 in the float argment. I could understand why it is 5 ,1.

Thanks a lot & Appreciate your effort,

Siva.
Back to top
View user's profile Send private message
prabs2006

Active User


Joined: 12 Jan 2006
Posts: 103

PostPosted: Fri Jan 20, 2006 6:00 pm    Post subject:
Reply with quote

Hi,

It is basically the argument to tell the total length and the decimals for the output.Here it is a total of 5 with one place to decimal.Please note that even '.' takes a single length. Thats why when we gave 4,1 it stopped with dot.

BTW, did u try with the other two options that I gave in the last post?
Didnt that work?

Thanks & Regards
Prabs
Back to top
View user's profile Send private message
sivassb

New User


Joined: 06 Jan 2006
Posts: 14

PostPosted: Sat Jan 21, 2006 2:52 pm    Post subject:
Reply with quote

Hi Prabhakaran,


Got it , Thanks a lot,

yes but for

1) SELECT SUM(A.WEEK1_AMT) / DEC(B.RATE)

gives me a different answer as

Total
----------------
545.93

3) SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE))

the result is


Total
---------
438

The second one you mentioned

SELECT Dec(SUM(A.WEEK1_AMT) / float(B.RATE),5,1)

gives the correct result as

TOTAL
--------
438.5


Thanks,
Siva
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Can I traverse a STEM variable? chong.zhou CLIST & REXX 5 Wed May 31, 2017 3:54 pm
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us