Cast query result as decimal in mysql
What I have:
- Mariadb version : 10.3.10
I have a table visits that has 2 columns userid and ref_url, where userid is the id of the users and ref_url corresponds to the url from which the userid was referred. Empty spaces in the column ref_url corresponds to direct visits and are not considered.
Table: vists
-------------------------------------
| userid | ref_url |
-------------------------------------
| 1 | |
| 1 | https://demosite.com |
| 1 | https://demosite2.com |
| 1 | |
| 1 | https://demosite3.com |
| 1 | |
-------------------------------------
What I expect to achieve:
I want to create a query that will count the total number of ref_url occurrences for a particular userid excluding empty spaces (in the ref_url field), limit the count within 100 and multiply the count with 0.5 and represent the result as DECIMAL on an alias column estimated value.
------------------------------------
| userid | estimated value |
------------------------------------
| 1 | 1.50 |
------------------------------------
My Query:
SELECT userid,
CAST(((
SELECT COUNT(NULLIF(TRIM(ref_url), ''))
FROM visits
LIMIT 0,100
) * 0.5) AS DECIMAL(12,2)) AS 'estimated value'
FROM visits
GROUP BY userid
The above query throws a syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2))
The query works when DECIMAL(12,2) is replaced by UNSIGNED.
The query when used with DECIMAL(12,2) outputs with Adminer but results in
[1329] No data - zero rows fetched, selected, or processed
when tried directly on Mariadb. Is there a chance of permission issues?
mysql mariadb
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
add a comment |
What I have:
- Mariadb version : 10.3.10
I have a table visits that has 2 columns userid and ref_url, where userid is the id of the users and ref_url corresponds to the url from which the userid was referred. Empty spaces in the column ref_url corresponds to direct visits and are not considered.
Table: vists
-------------------------------------
| userid | ref_url |
-------------------------------------
| 1 | |
| 1 | https://demosite.com |
| 1 | https://demosite2.com |
| 1 | |
| 1 | https://demosite3.com |
| 1 | |
-------------------------------------
What I expect to achieve:
I want to create a query that will count the total number of ref_url occurrences for a particular userid excluding empty spaces (in the ref_url field), limit the count within 100 and multiply the count with 0.5 and represent the result as DECIMAL on an alias column estimated value.
------------------------------------
| userid | estimated value |
------------------------------------
| 1 | 1.50 |
------------------------------------
My Query:
SELECT userid,
CAST(((
SELECT COUNT(NULLIF(TRIM(ref_url), ''))
FROM visits
LIMIT 0,100
) * 0.5) AS DECIMAL(12,2)) AS 'estimated value'
FROM visits
GROUP BY userid
The above query throws a syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2))
The query works when DECIMAL(12,2) is replaced by UNSIGNED.
The query when used with DECIMAL(12,2) outputs with Adminer but results in
[1329] No data - zero rows fetched, selected, or processed
when tried directly on Mariadb. Is there a chance of permission issues?
mysql mariadb
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
1
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17
add a comment |
What I have:
- Mariadb version : 10.3.10
I have a table visits that has 2 columns userid and ref_url, where userid is the id of the users and ref_url corresponds to the url from which the userid was referred. Empty spaces in the column ref_url corresponds to direct visits and are not considered.
Table: vists
-------------------------------------
| userid | ref_url |
-------------------------------------
| 1 | |
| 1 | https://demosite.com |
| 1 | https://demosite2.com |
| 1 | |
| 1 | https://demosite3.com |
| 1 | |
-------------------------------------
What I expect to achieve:
I want to create a query that will count the total number of ref_url occurrences for a particular userid excluding empty spaces (in the ref_url field), limit the count within 100 and multiply the count with 0.5 and represent the result as DECIMAL on an alias column estimated value.
------------------------------------
| userid | estimated value |
------------------------------------
| 1 | 1.50 |
------------------------------------
My Query:
SELECT userid,
CAST(((
SELECT COUNT(NULLIF(TRIM(ref_url), ''))
FROM visits
LIMIT 0,100
) * 0.5) AS DECIMAL(12,2)) AS 'estimated value'
FROM visits
GROUP BY userid
The above query throws a syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2))
The query works when DECIMAL(12,2) is replaced by UNSIGNED.
The query when used with DECIMAL(12,2) outputs with Adminer but results in
[1329] No data - zero rows fetched, selected, or processed
when tried directly on Mariadb. Is there a chance of permission issues?
mysql mariadb
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
What I have:
- Mariadb version : 10.3.10
I have a table visits that has 2 columns userid and ref_url, where userid is the id of the users and ref_url corresponds to the url from which the userid was referred. Empty spaces in the column ref_url corresponds to direct visits and are not considered.
Table: vists
-------------------------------------
| userid | ref_url |
-------------------------------------
| 1 | |
| 1 | https://demosite.com |
| 1 | https://demosite2.com |
| 1 | |
| 1 | https://demosite3.com |
| 1 | |
-------------------------------------
What I expect to achieve:
I want to create a query that will count the total number of ref_url occurrences for a particular userid excluding empty spaces (in the ref_url field), limit the count within 100 and multiply the count with 0.5 and represent the result as DECIMAL on an alias column estimated value.
------------------------------------
| userid | estimated value |
------------------------------------
| 1 | 1.50 |
------------------------------------
My Query:
SELECT userid,
CAST(((
SELECT COUNT(NULLIF(TRIM(ref_url), ''))
FROM visits
LIMIT 0,100
) * 0.5) AS DECIMAL(12,2)) AS 'estimated value'
FROM visits
GROUP BY userid
The above query throws a syntax error:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2))
The query works when DECIMAL(12,2) is replaced by UNSIGNED.
The query when used with DECIMAL(12,2) outputs with Adminer but results in
[1329] No data - zero rows fetched, selected, or processed
when tried directly on Mariadb. Is there a chance of permission issues?
mysql mariadb
mysql mariadb
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
edited Nov 26 '18 at 6:38
marc_s
580k13011191267
580k13011191267
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
asked Nov 15 '18 at 14:47
mesumosumesumosu
58111
58111
1
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17
add a comment |
1
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17
1
1
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17
add a comment |
1 Answer
1
active
oldest
votes
LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say
LIMIT ROWS EXAMINED 100
(Caveat: I have not tested this.)
Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21
As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use
FORMAT(expression, 2)
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)
– Rick James
Nov 15 '18 at 23:51
@mesumosu -SHOW VARIABLES LIKE 'div_precision_increment';Ref
– Rick James
Nov 15 '18 at 23:54
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%2f53321999%2fcast-query-result-as-decimal-in-mysql%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
LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say
LIMIT ROWS EXAMINED 100
(Caveat: I have not tested this.)
Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21
As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use
FORMAT(expression, 2)
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)
– Rick James
Nov 15 '18 at 23:51
@mesumosu -SHOW VARIABLES LIKE 'div_precision_increment';Ref
– Rick James
Nov 15 '18 at 23:54
add a comment |
LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say
LIMIT ROWS EXAMINED 100
(Caveat: I have not tested this.)
Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21
As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use
FORMAT(expression, 2)
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)
– Rick James
Nov 15 '18 at 23:51
@mesumosu -SHOW VARIABLES LIKE 'div_precision_increment';Ref
– Rick James
Nov 15 '18 at 23:54
add a comment |
LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say
LIMIT ROWS EXAMINED 100
(Caveat: I have not tested this.)
Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21
As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use
FORMAT(expression, 2)
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
LIMIT occurs after the data is gathered. Your subquery gathers only one row, so the LIMIT is irrelevant. However, since you are using MariaDB (not MySQL), you can say
LIMIT ROWS EXAMINED 100
(Caveat: I have not tested this.)
Ref: https://mariadb.com/kb/en/library/limit-rows-examined/ -- since MariaDB 5.5.21
As for DECIMAL -- Instead of CAST(expression AS DECIMAL(10,2)), use
FORMAT(expression, 2)
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
edited Nov 15 '18 at 21:39
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
answered Nov 15 '18 at 21:34
Rick JamesRick James
69.5k561102
69.5k561102
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)
– Rick James
Nov 15 '18 at 23:51
@mesumosu -SHOW VARIABLES LIKE 'div_precision_increment';Ref
– Rick James
Nov 15 '18 at 23:54
add a comment |
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)
– Rick James
Nov 15 '18 at 23:51
@mesumosu -SHOW VARIABLES LIKE 'div_precision_increment';Ref
– Rick James
Nov 15 '18 at 23:54
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
FORMAT nailed it!! So, would like to know why FORMAT did it, while CAST didn't? Is it because of a nested query ? One more thing, I think FORMAT returns the result as string value and not numeric, is that correct?
– mesumosu
Nov 15 '18 at 23:38
@mesumosu - I have not figured out why it went wrong.
FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)– Rick James
Nov 15 '18 at 23:51
@mesumosu - I have not figured out why it went wrong.
FORMAT, as the name implies, is a formatting function, hence returns a string. (It also adds 'thousands separators' in big numbers.)– Rick James
Nov 15 '18 at 23:51
@mesumosu -
SHOW VARIABLES LIKE 'div_precision_increment'; Ref– Rick James
Nov 15 '18 at 23:54
@mesumosu -
SHOW VARIABLES LIKE 'div_precision_increment'; Ref– Rick James
Nov 15 '18 at 23:54
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%2f53321999%2fcast-query-result-as-decimal-in-mysql%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
1
I can't reproduce your problem, at least not with what I am trying locally.
– Tim Biegeleisen
Nov 15 '18 at 14:52
What is your current DB Version? btw. could you try with float(12,2) ?
– Christian Felix
Nov 15 '18 at 14:53
This query runs fine with my MariaDB version 10.0.36-MariaDB-0ubuntu0.16.04.1. Which one do you use?
– Tobi
Nov 15 '18 at 15:01
it's working fine for me : sqlfiddle.com/#!9/6650d9e/1
– Sagar Gangwal
Nov 15 '18 at 15:04
@Christian Felix I tried float, same result. Surprisingly, query outputs with adminer, but gives no data fetched error when directly executed on Mariadb
– mesumosu
Nov 15 '18 at 15:17