Empty group by with count returns null
I have a query with a count/group by - If no person with the name 'Bob' is found then it returns no rows at all because the GROUP BY is on an empty set - null is returned for the count - I'd like to change the behaviour so that count returns 0 and not null.
select p.Id as personId, count(*) as alias1 from
Table1 as alias2
join Table2 as alias3 on alias3.personId = alias1.Id
where Name = 'Bob'
group by p.Id
sql
sql-server asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
add a comment |
I have a query with a count/group by - If no person with the name 'Bob' is found then it returns no rows at all because the GROUP BY is on an empty set - null is returned for the count - I'd like to change the behaviour so that count returns 0 and not null.
select p.Id as personId, count(*) as alias1 from
Table1 as alias2
join Table2 as alias3 on alias3.personId = alias1.Id
where Name = 'Bob'
group by p.Id
sql
sql-server asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
2
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13
add a comment |
I have a query with a count/group by - If no person with the name 'Bob' is found then it returns no rows at all because the GROUP BY is on an empty set - null is returned for the count - I'd like to change the behaviour so that count returns 0 and not null.
select p.Id as personId, count(*) as alias1 from
Table1 as alias2
join Table2 as alias3 on alias3.personId = alias1.Id
where Name = 'Bob'
group by p.Id
sql
sql-server asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
I have a query with a count/group by - If no person with the name 'Bob' is found then it returns no rows at all because the GROUP BY is on an empty set - null is returned for the count - I'd like to change the behaviour so that count returns 0 and not null.
select p.Id as personId, count(*) as alias1 from
Table1 as alias2
join Table2 as alias3 on alias3.personId = alias1.Id
where Name = 'Bob'
group by p.Id
sql
sql-server sql
sql-server asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
asked Nov 29 '18 at 0:00
J.DoeJ.Doe
213
213
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
2
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13
add a comment |
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
2
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
2
2
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13
add a comment |
2 Answers
2
active
oldest
votes
Your example was confusing because you're using some alias that don't exist.
Try to use LEFT JOIN.
with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as
(select 2 as personId)
select alias1.Id as personId, count(alias2.personId)
from Table1 as alias1
left join Table2 as alias2 on alias1.Id = alias2.personId
where Name = 'Bob'
group by alias1.Id
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
add a comment |
Please see:
Count Returning blank instead of 0
Essentially, you can't use GROUP BY and expect no results to return a row.
answered Nov 29 '18 at 0:10
Jared CJared C
237314
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%2f53529935%2fempty-group-by-with-count-returns-null%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
Your example was confusing because you're using some alias that don't exist.
Try to use LEFT JOIN.
with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as
(select 2 as personId)
select alias1.Id as personId, count(alias2.personId)
from Table1 as alias1
left join Table2 as alias2 on alias1.Id = alias2.personId
where Name = 'Bob'
group by alias1.Id
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
add a comment |
Your example was confusing because you're using some alias that don't exist.
Try to use LEFT JOIN.
with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as
(select 2 as personId)
select alias1.Id as personId, count(alias2.personId)
from Table1 as alias1
left join Table2 as alias2 on alias1.Id = alias2.personId
where Name = 'Bob'
group by alias1.Id
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
add a comment |
Your example was confusing because you're using some alias that don't exist.
Try to use LEFT JOIN.
with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as
(select 2 as personId)
select alias1.Id as personId, count(alias2.personId)
from Table1 as alias1
left join Table2 as alias2 on alias1.Id = alias2.personId
where Name = 'Bob'
group by alias1.Id
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
Your example was confusing because you're using some alias that don't exist.
Try to use LEFT JOIN.
with
Table1 as
(select 1 as Id, 'Bob' as Name),
Table2 as
(select 2 as personId)
select alias1.Id as personId, count(alias2.personId)
from Table1 as alias1
left join Table2 as alias2 on alias1.Id = alias2.personId
where Name = 'Bob'
group by alias1.Id
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
answered Nov 29 '18 at 0:08
Erika Madeiros SilvaErika Madeiros Silva
164
164
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
add a comment |
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
Thank you for providing an answer that compiles and executes with a sample data set.
– rschoenbach
Nov 29 '18 at 0:13
add a comment |
Please see:
Count Returning blank instead of 0
Essentially, you can't use GROUP BY and expect no results to return a row.
answered Nov 29 '18 at 0:10
Jared CJared C
237314
add a comment |
Please see:
Count Returning blank instead of 0
Essentially, you can't use GROUP BY and expect no results to return a row.
answered Nov 29 '18 at 0:10
Jared CJared C
237314
add a comment |
Please see:
Count Returning blank instead of 0
Essentially, you can't use GROUP BY and expect no results to return a row.
answered Nov 29 '18 at 0:10
Jared CJared C
237314
Please see:
Count Returning blank instead of 0
Essentially, you can't use GROUP BY and expect no results to return a row.
answered Nov 29 '18 at 0:10
Jared CJared C
237314
answered Nov 29 '18 at 0:10
Jared CJared C
237314
answered Nov 29 '18 at 0:10
Jared CJared C
237314
answered Nov 29 '18 at 0:10
Jared CJared C
237314
237314
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.
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%2f53529935%2fempty-group-by-with-count-returns-null%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
You're trying to join table 1 and table 2 by … comparing to a column in the SELECT statement? There's no join to actual table 2 here. So this isn't going to even remotely do what you think it will. Change your "alias1" in your ON clause to "alias2" and try again.
– pmbAustin
Nov 29 '18 at 0:08
2
Possible duplicate of Count Returning blank instead of 0
– Jared C
Nov 29 '18 at 0:13