Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpockets
Starting Member
45 Posts |
 Posted - 2007-05-01 : 15:11:25
|
| I'm very new to sql server and still don't know the in's and out's I have this: Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END)/ Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Per_QuotedI keep on getting 0, anybody have any ideas? |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-01 : 15:37:59
|
| Its getting the number of the count of the Records containing STATUS_CD = 'B' or 'K' and then its dividing with the Total count in the THIT_RATION_DETL table. If you can post the data also then some one over here can surely help you and also what is the requirement of this query...Chiraghttp://chirikworld.blogspot.com/ |
 |
|
|
jpockets
Starting Member
45 Posts |
Posted - 2007-05-01 : 15:45:49
|
| The first part Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END) returns 3789the second part Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Subreturns 14494 so 3789/14494 = .2614But i keep on getting 0, am i missing a step |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-01 : 15:47:10
|
You didn't read the whole link that I gave you, did you?  it says how to do this right there:quote:
Dividing Integers to calculate a Percentage or other result with decimal placesAccess: SELECT Int1 / Int2 (this returns a Double value implicitly)T-SQL: SELECT Int1 * 1.0 / Int2 (the multiplication by 1.0 results in a numeric(8,6) being returned)
just multiply the first SUM(...) by 1.0 before doing the divide. Otherwise, you are dividing two integers, so the result is also an integer.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jpockets
Starting Member
45 Posts |
Posted - 2007-05-01 : 15:57:43
|
Yep that was it, should have read more into that link  , thank you again... |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-01 : 16:00:21
|
Well, Jeff gave you the explanation on why you are getting zero.. So i guess this query should work for you .. Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END)/ (Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO) * 1.0 )as Per_Quoted Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-01 : 16:06:44
|
| No problem, just giving you a hard time. there's a lot there and it's not very well organized. Glad I could help!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
Division help