View previous topic :: :: View next topic

Author 
Message 
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 

Back to top 




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. 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


prabs2006
Active User
Joined: 12 Jan 2006 Posts: 103




Hi
Just try without that float..
Thanks & Regards
Prabhakaran 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


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 

Back to top 


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. 

Back to top 


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 

Back to top 


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 

Back to top 


