How do I get value closest to after dividing two integer in SQL server

Question

0.00/5 (No votes)

See more:

if value is 75.5 or more i want to return 76 and less than 75.5 I want to return 75.

This is what my query looks like

SELECT DISTRICT, 
COUNT(DISTRICT) RegionDistrict,
CEILING(SUM(CONVERT(int,[turnoverSM (%)]))/COUNT(DISTRICT)) as SM,
CEILING(SUM(CONVERT(int,[turnoverASM (%)]))/COUNT(DISTRICT)) as ASM
FROM HRStaffTable WHERE REGION = 40 AND Created_Date = CONVERT(DATE, GetDate()) GROUP BY DISTRICT

What I have tried:

SELECT CEILING(1060/14) as result

Posted 9-Feb-18 3:08am

istudent

Updated 9-Feb-18 3:18am

Add a Solution

Comments

Ziee-M 9-Feb-18 9:23am

You can create your own function that Rounds using your own rule

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-02-09T03:18:00

Solution 1

When you divide two integers, the result will always be truncated. If you want to round the result instead, then one of the values needs to be a floating-point type.

SQL

ROUND(CONVERT(real, SUM([turnoverSM (%)])) / COUNT(DISTRICT), 0)

ROUND (Transact-SQL) | Microsoft Docs[^]

Also, in case you haven't realised it yet, it's an extremely bad idea for table or column names to contain spaces or other special characters. If possible, you should redesign your database to rename the columns so that they only use alphanumeric characters.

Posted 9-Feb-18 3:18am

Richard Deeming

Updated 9-Feb-18 3:41am

v2

Comments

istudent 9-Feb-18 9:22am

I tested Select ROUND((1060/14),0) as Result , it still return 75

Richard Deeming 9-Feb-18 9:24am

Because both 1060 and 14 are integers!

Try: ROUND(1060. / 14, 0) - note the "." at the end of the first number.

istudent 9-Feb-18 9:38am

But there is no way for me to add decimal in my field in table. How do I achieve that? if you checked the column SUM([turnoverSM (%)]) is 1060

Richard Deeming 9-Feb-18 9:41am

OK, I thought it was a floating-point type, since you're explicitly converting it to int.

Try converting it to double instead:

ROUND(CONVERT(double, SUM([turnoverSM (%)])) / COUNT(DISTRICT), 0)

istudent 9-Feb-18 9:47am

I convert it to decimal, it return 76.000000, it worked but I think I should just return the sum and do calculation in code.

like this.

Dim CriticalIndicator = "1060"
Dim districtstorecount = "14"

Dim ci As Int32 = Convert.ToInt32(CriticalIndicator)
Dim dc As Int32 = Convert.ToInt32(districtstorecount)
Dim HelloPercentage = (ci / dc)

Console.WriteLine(String.Format("{0:N0}", HelloPercentage))

istudent 9-Feb-18 9:49am

converting to real, worked. Thank you very much sir.

Maciej Los 9-Feb-18 14:47pm

5ed!