SQL - Why does my output from subqueries display my answer multiple times?
The output to the following query is repeated for the number of rows in my table. Can anyone explain why and if using 'Limit 1' to solve this is a good habit to get into?
SELECT
CAST((SELECT COUNT(event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01 AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01) AS float)
* 100.0
FROM creation_funnel;
mysql sql subquery
edited Nov 11 at 22:26
Strawberry
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
add a comment |
The output to the following query is repeated for the number of rows in my table. Can anyone explain why and if using 'Limit 1' to solve this is a good habit to get into?
SELECT
CAST((SELECT COUNT(event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01 AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01) AS float)
* 100.0
FROM creation_funnel;
mysql sql subquery
edited Nov 11 at 22:26
Strawberry
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
Try removing theFROM creation_funnelat the end
– Mohammad C
Nov 11 at 22:20
3
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40
add a comment |
The output to the following query is repeated for the number of rows in my table. Can anyone explain why and if using 'Limit 1' to solve this is a good habit to get into?
SELECT
CAST((SELECT COUNT(event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01 AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01) AS float)
* 100.0
FROM creation_funnel;
mysql sql subquery
edited Nov 11 at 22:26
Strawberry
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
The output to the following query is repeated for the number of rows in my table. Can anyone explain why and if using 'Limit 1' to solve this is a good habit to get into?
SELECT
CAST((SELECT COUNT(event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01 AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id)
FROM creation_funnel
WHERE Ds = 2018-10-01) AS float)
* 100.0
FROM creation_funnel;
mysql sql subquery
mysql sql subquery
edited Nov 11 at 22:26
Strawberry
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
edited Nov 11 at 22:26
Strawberry
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
edited Nov 11 at 22:26
Strawberry
25.8k83149
edited Nov 11 at 22:26
Strawberry
25.8k83149
edited Nov 11 at 22:26
Strawberry
25.8k83149
25.8k83149
asked Nov 11 at 22:15
user2229066
80312
asked Nov 11 at 22:15
user2229066
80312
asked Nov 11 at 22:15
user2229066
80312
80312
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
Try removing theFROM creation_funnelat the end
– Mohammad C
Nov 11 at 22:20
3
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40
add a comment |
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
Try removing theFROM creation_funnelat the end
– Mohammad C
Nov 11 at 22:20
3
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
Try removing the
FROM creation_funnel at the end– Mohammad C
Nov 11 at 22:20
Try removing the
FROM creation_funnel at the end– Mohammad C
Nov 11 at 22:20
3
3
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40
add a comment |
2 Answers
2
active
oldest
votes
This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:
SELECT
COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel
Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.
This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.
answered Nov 11 at 23:14
Nick
23.5k91535
add a comment |
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0
The repetition is because of the FROM part of the statement. Remove it.
Your are getting multiple results because the select is being run for each record in the table.
updated
Based on what nick said. You could simplify his answer to the following.
SELECT
COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) /
COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
answered Nov 11 at 22:21
Mohammad C
1,1831312
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%2f53253784%2fsql-why-does-my-output-from-subqueries-display-my-answer-multiple-times%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:
SELECT
COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel
Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.
This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.
answered Nov 11 at 23:14
Nick
23.5k91535
add a comment |
This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:
SELECT
COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel
Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.
This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.
answered Nov 11 at 23:14
Nick
23.5k91535
add a comment |
This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:
SELECT
COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel
Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.
This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.
answered Nov 11 at 23:14
Nick
23.5k91535
This query would be a good candidate for conditional aggregation as it will enable you to get rid of the two subqueries. Try this instead:
SELECT
COUNT(CASE WHEN Ds = '2018-10-01' AND action = 'publish' THEN event_id END) /
COUNT(DISTINCT CASE WHEN Ds = '2018-10-01' THEN event_id END) * 100 AS percent_published
FROM creation_funnel
Note that date strings need to be enclosed in quotes, as @Strawberry points out, 2018-10-01 is interpreted as an integer expression, evaluating to 2007.
This query will also only give you one result compared to your query, which will give you a result for every row in the table. Either removing the FROM clause, or adding LIMIT 1 would resolve that, but using conditional aggregation is a better alternative.
answered Nov 11 at 23:14
Nick
23.5k91535
answered Nov 11 at 23:14
Nick
23.5k91535
answered Nov 11 at 23:14
Nick
23.5k91535
answered Nov 11 at 23:14
Nick
23.5k91535
23.5k91535
add a comment |
add a comment |
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0
The repetition is because of the FROM part of the statement. Remove it.
Your are getting multiple results because the select is being run for each record in the table.
updated
Based on what nick said. You could simplify his answer to the following.
SELECT
COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) /
COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
answered Nov 11 at 22:21
Mohammad C
1,1831312
add a comment |
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0
The repetition is because of the FROM part of the statement. Remove it.
Your are getting multiple results because the select is being run for each record in the table.
updated
Based on what nick said. You could simplify his answer to the following.
SELECT
COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) /
COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
answered Nov 11 at 22:21
Mohammad C
1,1831312
add a comment |
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0
The repetition is because of the FROM part of the statement. Remove it.
Your are getting multiple results because the select is being run for each record in the table.
updated
Based on what nick said. You could simplify his answer to the following.
SELECT
COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) /
COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
answered Nov 11 at 22:21
Mohammad C
1,1831312
SELECT
CAST((SELECT COUNT(event_id) FROM creation_funnel WHERE Ds = '2018-10-01' AND action = "publish") AS float) /
CAST((SELECT COUNT(DISTINCT event_id) FROM creation_funnel WHERE Ds = '2018-10-01') AS float) * 100.0
The repetition is because of the FROM part of the statement. Remove it.
Your are getting multiple results because the select is being run for each record in the table.
updated
Based on what nick said. You could simplify his answer to the following.
SELECT
COUNT(CASE WHEN action = 'publish' THEN event_id ELSE 0 END) /
COUNT(DISTINCT event_id) * 100 AS percent_published
FROM creation_funnel
WHERE Ds = '2018-10-01';
answered Nov 11 at 22:21
Mohammad C
1,1831312
edited Nov 12 at 19:12
answered Nov 11 at 22:21
Mohammad C
1,1831312
answered Nov 11 at 22:21
Mohammad C
1,1831312
answered Nov 11 at 22:21
Mohammad C
1,1831312
1,1831312
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%2f53253784%2fsql-why-does-my-output-from-subqueries-display-my-answer-multiple-times%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
I am not sure, but it looks strange to me, that you are using aggregation function Count without the GROUP BY statement.
– Patrik Valkovič
Nov 11 at 22:18
Try removing the
FROM creation_funnelat the end– Mohammad C
Nov 11 at 22:20
3
2018-10-01 = 2007
– Strawberry
Nov 11 at 22:40