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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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

pjnithin · Active User Joined: 22 Dec 2005 Posts: 116

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.

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

Hi Prabs,

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

Thanks,
siva

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

Hi
Just try without that float..

Thanks & Regards
Prabhakaran

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

Hi,

Fine...Lets try this one below..

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

Thanks & Regards

sivassb · New User Joined: 06 Jan 2006 Posts: 14

Hi,

I'm getting the Same Error message

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

thanks,
Siva

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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.

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

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

sivassb · New User Joined: 06 Jan 2006 Posts: 14

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