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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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