Mixing
Trying to round a value which results from 2 multiplied fields
In SQL Server, I am trying to round a value which results from 2 multiplied fields t.hourlyRate and t.hours. The sum is calculated as:
SUM(t.hours * t.hourlyRate) AS tot_cost
The results gives me values such 33.2330 or 51.6648 whereas I just want 33.2 or 51.7.
I've just variants of cast/round etc to no avail - any ideas please
sql
sql-server tsql numbers edited Nov 20 '18 at 10:03
Salman A
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
add a comment |
In SQL Server, I am trying to round a value which results from 2 multiplied fields t.hourlyRate and t.hours. The sum is calculated as:
SUM(t.hours * t.hourlyRate) AS tot_cost
The results gives me values such 33.2330 or 51.6648 whereas I just want 33.2 or 51.7.
I've just variants of cast/round etc to no avail - any ideas please
sql
sql-server tsql numbers edited Nov 20 '18 at 10:03
Salman A
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48
add a comment |
In SQL Server, I am trying to round a value which results from 2 multiplied fields t.hourlyRate and t.hours. The sum is calculated as:
SUM(t.hours * t.hourlyRate) AS tot_cost
The results gives me values such 33.2330 or 51.6648 whereas I just want 33.2 or 51.7.
I've just variants of cast/round etc to no avail - any ideas please
sql
sql-server tsql numbers edited Nov 20 '18 at 10:03
Salman A
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
In SQL Server, I am trying to round a value which results from 2 multiplied fields t.hourlyRate and t.hours. The sum is calculated as:
SUM(t.hours * t.hourlyRate) AS tot_cost
The results gives me values such 33.2330 or 51.6648 whereas I just want 33.2 or 51.7.
I've just variants of cast/round etc to no avail - any ideas please
sql
sql-server tsql numbers sql
sql-server tsql numbers edited Nov 20 '18 at 10:03
Salman A
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
edited Nov 20 '18 at 10:03
Salman A
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
edited Nov 20 '18 at 10:03
Salman A
176k66336424
edited Nov 20 '18 at 10:03
Salman A
176k66336424
edited Nov 20 '18 at 10:03
Salman A
176k66336424
176k66336424
asked Nov 19 '18 at 14:45
matt80
184
asked Nov 19 '18 at 14:45
matt80
184
asked Nov 19 '18 at 14:45
matt80
184
184
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48
add a comment |
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48
add a comment |
4 Answers
4
active
oldest
votes
You can cast() the result like this
cast(sum(t.hours * t.hourlyRate) as decimal(10,1))
answered Nov 19 '18 at 14:46
juergen d
159k24200260
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
add a comment |
Use ROUND function, which starts with SQL Server 2008 , upto precision 1 as :
SELECT ROUND(33.2330, 1) AS RoundValue1,
ROUND(51.6648, 1) AS RoundValue2;
RoundValue1 RoundValue2
----------- -----------
33.2 51.7
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
add a comment |
If you are using SQL Server 2012 or later, then FORMAT is an option:
FORMAT(SUM(t.hours * t.hourlyRate), 'N1') AS tot_cost
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
add a comment |
Use one of the following depending on whether you want to round individual values or the overall sum:
SELECT SUM(CAST(t.hourlyRate * t.hours AS DECIMAL(10, 1))) AS tot_cost
SELECT CAST(SUM(t.hourlyRate * t.hours) AS DECIMAL(10, 1)) AS tot_cost
Both could produce sightly different results.
answered Nov 19 '18 at 15:41
Salman A
176k66336424
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377041%2ftrying-to-round-a-value-which-results-from-2-multiplied-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can cast() the result like this
cast(sum(t.hours * t.hourlyRate) as decimal(10,1))
answered Nov 19 '18 at 14:46
juergen d
159k24200260
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
add a comment |
You can cast() the result like this
cast(sum(t.hours * t.hourlyRate) as decimal(10,1))
answered Nov 19 '18 at 14:46
juergen d
159k24200260
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
add a comment |
You can cast() the result like this
cast(sum(t.hours * t.hourlyRate) as decimal(10,1))
answered Nov 19 '18 at 14:46
juergen d
159k24200260
You can cast() the result like this
cast(sum(t.hours * t.hourlyRate) as decimal(10,1))
answered Nov 19 '18 at 14:46
juergen d
159k24200260
answered Nov 19 '18 at 14:46
juergen d
159k24200260
answered Nov 19 '18 at 14:46
juergen d
159k24200260
answered Nov 19 '18 at 14:46
juergen d
159k24200260
159k24200260
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
add a comment |
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
Thanks - this answered my query.
– matt80
Nov 19 '18 at 15:50
add a comment |
Use ROUND function, which starts with SQL Server 2008 , upto precision 1 as :
SELECT ROUND(33.2330, 1) AS RoundValue1,
ROUND(51.6648, 1) AS RoundValue2;
RoundValue1 RoundValue2
----------- -----------
33.2 51.7
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
add a comment |
Use ROUND function, which starts with SQL Server 2008 , upto precision 1 as :
SELECT ROUND(33.2330, 1) AS RoundValue1,
ROUND(51.6648, 1) AS RoundValue2;
RoundValue1 RoundValue2
----------- -----------
33.2 51.7
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
add a comment |
Use ROUND function, which starts with SQL Server 2008 , upto precision 1 as :
SELECT ROUND(33.2330, 1) AS RoundValue1,
ROUND(51.6648, 1) AS RoundValue2;
RoundValue1 RoundValue2
----------- -----------
33.2 51.7
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
Use ROUND function, which starts with SQL Server 2008 , upto precision 1 as :
SELECT ROUND(33.2330, 1) AS RoundValue1,
ROUND(51.6648, 1) AS RoundValue2;
RoundValue1 RoundValue2
----------- -----------
33.2 51.7
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 14:49
Barbaros Özhan
12.3k71530
12.3k71530
add a comment |
add a comment |
If you are using SQL Server 2012 or later, then FORMAT is an option:
FORMAT(SUM(t.hours * t.hourlyRate), 'N1') AS tot_cost
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
add a comment |
If you are using SQL Server 2012 or later, then FORMAT is an option:
FORMAT(SUM(t.hours * t.hourlyRate), 'N1') AS tot_cost
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
add a comment |
If you are using SQL Server 2012 or later, then FORMAT is an option:
FORMAT(SUM(t.hours * t.hourlyRate), 'N1') AS tot_cost
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
If you are using SQL Server 2012 or later, then FORMAT is an option:
FORMAT(SUM(t.hours * t.hourlyRate), 'N1') AS tot_cost
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 14:47
Tim Biegeleisen
218k1387140
218k1387140
add a comment |
add a comment |
Use one of the following depending on whether you want to round individual values or the overall sum:
SELECT SUM(CAST(t.hourlyRate * t.hours AS DECIMAL(10, 1))) AS tot_cost
SELECT CAST(SUM(t.hourlyRate * t.hours) AS DECIMAL(10, 1)) AS tot_cost
Both could produce sightly different results.
answered Nov 19 '18 at 15:41
Salman A
176k66336424
add a comment |
Use one of the following depending on whether you want to round individual values or the overall sum:
SELECT SUM(CAST(t.hourlyRate * t.hours AS DECIMAL(10, 1))) AS tot_cost
SELECT CAST(SUM(t.hourlyRate * t.hours) AS DECIMAL(10, 1)) AS tot_cost
Both could produce sightly different results.
answered Nov 19 '18 at 15:41
Salman A
176k66336424
add a comment |
Use one of the following depending on whether you want to round individual values or the overall sum:
SELECT SUM(CAST(t.hourlyRate * t.hours AS DECIMAL(10, 1))) AS tot_cost
SELECT CAST(SUM(t.hourlyRate * t.hours) AS DECIMAL(10, 1)) AS tot_cost
Both could produce sightly different results.
answered Nov 19 '18 at 15:41
Salman A
176k66336424
Use one of the following depending on whether you want to round individual values or the overall sum:
SELECT SUM(CAST(t.hourlyRate * t.hours AS DECIMAL(10, 1))) AS tot_cost
SELECT CAST(SUM(t.hourlyRate * t.hours) AS DECIMAL(10, 1)) AS tot_cost
Both could produce sightly different results.
answered Nov 19 '18 at 15:41
Salman A
176k66336424
edited Nov 20 '18 at 10:09
answered Nov 19 '18 at 15:41
Salman A
176k66336424
answered Nov 19 '18 at 15:41
Salman A
176k66336424
answered Nov 19 '18 at 15:41
Salman A
176k66336424
176k66336424
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377041%2ftrying-to-round-a-value-which-results-from-2-multiplied-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you using decimal or float data type?
– Salman A
Nov 19 '18 at 15:48