June 16, 2016 at 11:13 am
Hi all,
I have a table with following data:
idcnttotal
20118015464
233115464
27615464
282215464
47415464
I need to come up with 4th column that is: cnt/total, but it gives me 0.
What do I need to "cast" my expression to see the actual number no matter how small that is?
June 16, 2016 at 11:16 am
what are the datatypes for columns "cnt" and "total"?
what are you expected results?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 16, 2016 at 11:19 am
maybe.....
SELECT id, cnt, total, cnt*1.0/total
FROM <yourtable>
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 16, 2016 at 1:30 pm
rightontarget (6/16/2016)
Hi all,I have a table with following data:
idcnttotal20118015464
233115464
27615464
282215464
47415464
I need to come up with 4th column that is: cnt/total, but it gives me 0.
What do I need to "cast" my expression to see the actual number no matter how small that is?
Based on JLS's code, do you now understand why you were coming up with 0? I ask because it's a hugely important concept that can either bite you or save you depending on what you're trying to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2016 at 1:53 pm
Not really, please explain
June 16, 2016 at 2:51 pm
rightontarget (6/16/2016)
Not really, please explain
suggest you research "MS SQL Integer division"
heres some code to play around with
CREATE TABLE #mytable(
cnt INTEGER NOT NULL
,total INTEGER NOT NULL
);
INSERT INTO #mytable(cnt,total) VALUES (1180,15464);
INSERT INTO #mytable(cnt,total) VALUES (31,15464);
INSERT INTO #mytable(cnt,total) VALUES (6,15464);
INSERT INTO #mytable(cnt,total) VALUES (22,15464);
INSERT INTO #mytable(cnt,total) VALUES (4,15464);
SELECT cnt,
total,
cnt / total as A,
cnt * 1.0 / total as B,
cnt/(total*100.0) as C,
cnt/total*100.0 as D, --- note the difference between col C and col D
CAST(cnt AS DECIMAL(9, 2)) / total as E,
cnt / CAST(total AS NUMERIC(7, 1)) as F,
CAST((cnt * 1.0 / total) as decimal (9,3)) as G
FROM #mytable;
DROP TABLE #mytable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 17, 2016 at 5:32 am
J Livingston SQL (6/16/2016)
rightontarget (6/16/2016)
Not really, please explainsuggest you research "MS SQL Integer division"
[/code]
Or even just plan "integer division" as this should be a basic math concept. Whole number division might also help you. But to put it another way...
Say you have an 8 oz cup for your 20 oz soda. If you divide 8 into 20 (20/8), you'll get an answer of 2 because 2 * 8 = 16 (two cups). The 20 oz bottle holds two cups of soda. Yes there is more soda left over but we're not concerned with fractions of a cup. So when you drink the bottle, you know you're drinking two full cups.
But if you want to figure out how many times 20 oz goes into an 8 oz cup, you have a problem. Because you can't pour a 20 oz bottle of soda into an 8 oz cup. It'll overflow. Therefore, when you're just looking the whole number, trying to stuff a bigger item into a smaller item, you can't. The whole of the bigger item just won't fit into the smaller item at all unless you change the way you're looking at the numbers.
Which is where other data types like decimal and float come in. Try casting your numbers as decimals before the division and see what happens.
And now I have an example of those stupid junior high school story problems in math class actually being useful in a real world situation. I don't know whether to apologize to my math teachers or to pretend I didn't just make up my own story problem. :crazy:
June 17, 2016 at 7:09 am
rightontarget (6/16/2016)
Not really, please explain
To summarize, if all parts of an equation are of the INTEGER datatype (or any integer based datatype), all of the math will be done using only integer math. That is, the concept of decimal places does NOT exist in integer math. It's like the math we originally learned in grade school, especially when it comes to division. Only a whole number result is returned and there might be a whole number for a remainder.
For example... take the simple problem of 1/3. Not having defined the datatype of the 1 or the 3, SQL Server looks at those two numbers, sees no decimal places, and makes the assumption that both are integers. In grade school math, that would be "1 divided by 3 is 0 with a remainder of 1" and, discarding the remainder, a 0 is returned as the answer.
If you take the equally simple problem of 1.0/3, SQL Server looks at that and realizes that at least one of the values has a decimal place in it even if the value to the right of the decimal point is .0. SQL Server then treats the whole problem as one type of decimal place math or another returning some flavor of 0.333333 as the result.
JLS's solution used *1.0 to force SQL Server to make the decision that Decimal-place math should occur because the constant of 1.0 has a decimal place in it. Because he multiplied by 1, it doesn't actually change the "value" of the formula... just the way it's calculated.
In the first problem of 1/3, which is the integer math version, you CAN actually get the Remmainder from the calculation. You just need to change the operator to "%". That's the "Modulus" operator which, as an over-simplified explanation, does nothing more than the same division as the "/" except, instead or returning the Quotient, it returns the REMAINDER from the Integer division. It can also return the Remainder from a Decimal Point division but that's a whole 'nuther subject. So 1%3 will do the same as before. It will calculate "1 divided by 3 is 0 with a remainder of 1" and return the Remainder of 1 rather than the Quotient of 0.
Again, understanding both of these concepts using Integer math is a powerful tool that can be used for all sorts of things but, if you don't understand it, you're left with wondering why 1/3 returns a 0 instead of 0.333333.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2016 at 11:19 am
Thank you very much everyone
June 21, 2016 at 4:54 am
rightontarget (6/20/2016)
Thank you very much everyone
Did any of the explanations actually help you out or do you need more information?
June 22, 2016 at 10:34 am
I think I got it, thank you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply