July 4, 2015 at 4:42 am
hi,
please tel me which one is good method to convert a following division into 2 decimal digits.
q1)
select cast(( cast( sum(population) as decimal) * 100) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state
q2)
select cast(( sum(population) * cast( 100 as decimal)) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state
q3)
select cast( (sum(population) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q4)
select cast(( cast(sum(population) as decimal) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q5)
select cast( (cast(sum(population) as decimal) * cast(100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q6)
select cast(( cast(sum(population) * 100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
please give some reasons
yours sincerly
July 4, 2015 at 9:32 am
Actually, they're all pretty bad. For one, you don't define what the precision or scale for what a lot of your conversions to DECIMAL are. That probably won't cause a problem in this example. It's just a really annoying practice that some folks have taken to, IMHO.
To be honest though, we have no idea what the table or data looks like and I can't imagine each row having a "totalpopulation" column along with a "population" column especially if there are multiple rows for each state unless there's some esoteric knowledge missing in your description. For example, I might see this if each row were (for example) a city listing and "totalpopulation" were made up of several other population columns perhaps with the number of people by ethnicity or educational level.
Since you're multiplying the numerator of each division by 100, it would also seem that you're looking to find what the percentage of "population" is compared to "total population" and I don't understand the need for the final result to be converted to a Decimal(7,2). If your intent is to list percentage, then the largest you would need is only Decimal(5,2), which would certainly be large enough hold the predicted maximum value of 100.00 percent.
Oddly enough, you've possibly missed one implicit conversion that you might take advantage of and that's on the number 100 itself. Not knowing anything about your actual data or the intent of the code other than to come up with a percentage and with the understanding that I think that the sum of "population" will always be less than the sum of "totalpopulation", I'd likely use the following code.
SELECT SomePercent = CAST(SUM([Population])*100.00/SUM(TotalPopulation) AS DECIMAL(5,2))
FROM dbo.Table1
GROUP BY [State]
;
Note that the 100.00 will force an implicit conversion of the whole problem to a "NUMERIC" value so that you don't end up with the zero-result that integer division would give you. Also notice the use of the brackets to guarantee that the reserved words of "population" and "state" won't become a problem in the future.
Now, if the "TotalPopulation" column could ever return a sum of zero, then you might want to protect against the "Divide by Zero" problem through the use of NULLIF which, in this case, will cause the denominator to become NULL if the sum of "TotalPopulation" is ever a zero and thus cause the entire answer for the given result to return a NULL. Like this...
SELECT SomePercent = CAST(SUM([Population])*100.00/NULLIF(SUM(TotalPopulation),0) AS DECIMAL(5,2))
FROM dbo.Table1
GROUP BY [State]
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2015 at 2:08 am
Basically example is dummy, i just wanted to undersand conversion to decimal so that i can get results in decimal.
why i have not taken any precision because , it will all ways be a whole number.
yours sincerely
July 26, 2015 at 5:34 am
Always specify your required precision or length. The default isn't 0, the default is DECIMAL(38,18), which is huge.
Cast either (or both) the numerator and denominator to DECIMAL of the required precision and you'll be fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2015 at 6:54 am
I got the point,
q1) that means if population is going to be whole number with out fraction.
that is converting using cast( '323' as decimal ) is write, because it has precision not the scale.
and after decision, the result will automaticaly be with precision and scale which is againa resticted to decimal(7,2)
because that was out need.
please correct me?
2) please also explain me what does it mean practically
https://msdn.microsoft.com/en-IN/library/ms187746.aspx
"For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example,decimal(5,5) and decimal(5,0) are considered different data types."
yours sincerely
July 27, 2015 at 7:06 am
rajemessage 14195 (7/27/2015)
I got the point,q1) that means if population is going to be whole number with out fraction.
that is converting using cast( '323' as decimal ) is write, because it has precision not the scale.
No, it's not right. It's lazy. The fact that the value you're casting has no decimals doesn't change what the default for the decimal is if you don't specify it.
and after decision, the result will automaticaly be with precision and scale which is againa resticted to decimal(7,2)
because that was out need.
No, you didn't specify the precision or scale, hence you get a DECIMAL(38,18), not DECIMAL(7,2)
Always specify precision, scale and lengths. That way you avoid depending on defaults that you probably aren't familiar with
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply