Display only 2 decimal places in MySQL (phpMyAdmin) but retain all decimal places for calculations
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have several tables in my MySQL database where I use Float type of columns, now based on these columns I have several other calculated columns too. I want to be able to continue using float columns for all my calculations and retain the "accuracy" yet when I see the results in phpMyAdmin, I want to see just 2 decimal places so that it looks neat.
TRUNCATE(0.16666, 2) would not work because it will evaluate to 0.16, however I still want to continue using 0.16666 for all my calculations.
ROUND(0.16666, 2) would evaluate to 0.17.
What I am looking for is for MySQL to continue using 0.16666 for any subsequent calculations, however when I select/display the table in phpMyAdmin it neatly displays just two decimal palces. Can you please advise.
mysql sql phpmyadmin
asked Nov 29 '18 at 6:35
QuarkQuark
317
add a comment |
I have several tables in my MySQL database where I use Float type of columns, now based on these columns I have several other calculated columns too. I want to be able to continue using float columns for all my calculations and retain the "accuracy" yet when I see the results in phpMyAdmin, I want to see just 2 decimal places so that it looks neat.
TRUNCATE(0.16666, 2) would not work because it will evaluate to 0.16, however I still want to continue using 0.16666 for all my calculations.
ROUND(0.16666, 2) would evaluate to 0.17.
What I am looking for is for MySQL to continue using 0.16666 for any subsequent calculations, however when I select/display the table in phpMyAdmin it neatly displays just two decimal palces. Can you please advise.
mysql sql phpmyadmin
asked Nov 29 '18 at 6:35
QuarkQuark
317
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places doselect tochar(colname,'99.99');
– nikhil sugandh
Nov 29 '18 at 6:50
3
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
1
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10
add a comment |
I have several tables in my MySQL database where I use Float type of columns, now based on these columns I have several other calculated columns too. I want to be able to continue using float columns for all my calculations and retain the "accuracy" yet when I see the results in phpMyAdmin, I want to see just 2 decimal places so that it looks neat.
TRUNCATE(0.16666, 2) would not work because it will evaluate to 0.16, however I still want to continue using 0.16666 for all my calculations.
ROUND(0.16666, 2) would evaluate to 0.17.
What I am looking for is for MySQL to continue using 0.16666 for any subsequent calculations, however when I select/display the table in phpMyAdmin it neatly displays just two decimal palces. Can you please advise.
mysql sql phpmyadmin
asked Nov 29 '18 at 6:35
QuarkQuark
317
I have several tables in my MySQL database where I use Float type of columns, now based on these columns I have several other calculated columns too. I want to be able to continue using float columns for all my calculations and retain the "accuracy" yet when I see the results in phpMyAdmin, I want to see just 2 decimal places so that it looks neat.
TRUNCATE(0.16666, 2) would not work because it will evaluate to 0.16, however I still want to continue using 0.16666 for all my calculations.
ROUND(0.16666, 2) would evaluate to 0.17.
What I am looking for is for MySQL to continue using 0.16666 for any subsequent calculations, however when I select/display the table in phpMyAdmin it neatly displays just two decimal palces. Can you please advise.
mysql sql phpmyadmin
mysql sql phpmyadmin
asked Nov 29 '18 at 6:35
QuarkQuark
317
asked Nov 29 '18 at 6:35
QuarkQuark
317
asked Nov 29 '18 at 6:35
QuarkQuark
317
asked Nov 29 '18 at 6:35
QuarkQuark
317
asked Nov 29 '18 at 6:35
QuarkQuark
317
317
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places doselect tochar(colname,'99.99');
– nikhil sugandh
Nov 29 '18 at 6:50
3
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
1
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10
add a comment |
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places doselect tochar(colname,'99.99');
– nikhil sugandh
Nov 29 '18 at 6:50
3
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
1
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places do
select tochar(colname,'99.99');– nikhil sugandh
Nov 29 '18 at 6:50
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places do
select tochar(colname,'99.99');– nikhil sugandh
Nov 29 '18 at 6:50
3
3
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
1
1
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10
add a comment |
1 Answer
1
active
oldest
votes
It is not possible to do this with just the tables, you have to decide if you want the precision or the rounded results.
If you want to columns with roundings create VIEWS for displaying your table.
answered Nov 29 '18 at 8:37
halliballihalliballi
865
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
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%2f53533142%2fdisplay-only-2-decimal-places-in-mysql-phpmyadmin-but-retain-all-decimal-place%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is not possible to do this with just the tables, you have to decide if you want the precision or the rounded results.
If you want to columns with roundings create VIEWS for displaying your table.
answered Nov 29 '18 at 8:37
halliballihalliballi
865
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
add a comment |
It is not possible to do this with just the tables, you have to decide if you want the precision or the rounded results.
If you want to columns with roundings create VIEWS for displaying your table.
answered Nov 29 '18 at 8:37
halliballihalliballi
865
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
add a comment |
It is not possible to do this with just the tables, you have to decide if you want the precision or the rounded results.
If you want to columns with roundings create VIEWS for displaying your table.
answered Nov 29 '18 at 8:37
halliballihalliballi
865
It is not possible to do this with just the tables, you have to decide if you want the precision or the rounded results.
If you want to columns with roundings create VIEWS for displaying your table.
answered Nov 29 '18 at 8:37
halliballihalliballi
865
answered Nov 29 '18 at 8:37
halliballihalliballi
865
answered Nov 29 '18 at 8:37
halliballihalliballi
865
answered Nov 29 '18 at 8:37
halliballihalliballi
865
865
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
add a comment |
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
Thank you @halliballi. I think I will have to bite the bullet and use VIEWS, I wanted to avoid it as they use up space on the database.
– Quark
Nov 29 '18 at 10:50
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.
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%2f53533142%2fdisplay-only-2-decimal-places-in-mysql-phpmyadmin-but-retain-all-decimal-place%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
for calculations you need different and for seeing you need diffrent what is saved that only willl be visible its not possible. you have to save like 0.16666 annd when need ed two decimal places do
select tochar(colname,'99.99');– nikhil sugandh
Nov 29 '18 at 6:50
3
In this case do the rounding in the application code, just before displaying the value.
– Shadow
Nov 29 '18 at 6:51
You seem to want your query tool to auto format float type columns for display using 2 dp, regardless of how many dp the data is stored as in the database, in a similar fashion to a query tool that has a default date format setting whereby date columns are presented in the format of your choice. If phpmyadmin doesn't have a way (in the settings) of specifying that you want float/decimal columns to be formatted as 2 decimal places you're going to need to look for a query tool that does. StackOverflow has specific rules against questions seeking software recommendations though..
– Caius Jard
Nov 29 '18 at 9:03
I'm voting to close this question as off-topic because answers would have to recommend particular MySQL query software, and "recommend me a" questions are off topic
– Caius Jard
Nov 29 '18 at 9:06
1
(Notwithstanding what I said the above, you can just use those functions every time you select data in phpmyadmin: Using TRUNC or ROUND in the select list part of your query does not alter the underlying data. MySQL will use th precise form and you format your data when you select it in the outermost select query)
– Caius Jard
Nov 29 '18 at 9:10