How can I return different value based on the selected results?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a very simple query like this:
SELECT Name FROM Users WHERE Age < 20;
I want to implement such a feature:
- If the result contains no value, return an empty string ''.
- If the result contains only 1 value, return the value.
- If the result contains more than 1 value, return a constant string 'Multiple'.
How can I implement this with a very simple query? Thanks for your help!
sql
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
add a comment |
I have a very simple query like this:
SELECT Name FROM Users WHERE Age < 20;
I want to implement such a feature:
- If the result contains no value, return an empty string ''.
- If the result contains only 1 value, return the value.
- If the result contains more than 1 value, return a constant string 'Multiple'.
How can I implement this with a very simple query? Thanks for your help!
sql
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
add a comment |
I have a very simple query like this:
SELECT Name FROM Users WHERE Age < 20;
I want to implement such a feature:
- If the result contains no value, return an empty string ''.
- If the result contains only 1 value, return the value.
- If the result contains more than 1 value, return a constant string 'Multiple'.
How can I implement this with a very simple query? Thanks for your help!
sql
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
I have a very simple query like this:
SELECT Name FROM Users WHERE Age < 20;
I want to implement such a feature:
- If the result contains no value, return an empty string ''.
- If the result contains only 1 value, return the value.
- If the result contains more than 1 value, return a constant string 'Multiple'.
How can I implement this with a very simple query? Thanks for your help!
sql
sql
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
asked Nov 17 '18 at 3:06
Jesse ZhangJesse Zhang
11
11
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
You can use something like CASE in SELECT section to modify your returned result
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
add a comment |
The following query can help you out with the 3 conditions.
Instead of an empty string i put in 'None' for sake of clarity. You may edit it to be an empty string..
select case when count(*)=0 then 'None'
when count(name)=1 then max(name)
when count(name)>1 then max('Multiple')
end as name
from users1
where age <20
See a full demo with all the 3 test cases being catered to
- No records with age <20
- Exactly one record with age < 20
- More than one record with age <20
Demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=929d6ffca27ab5c4c05eeabdb6dd8b12
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
add a comment |
I am not sure what "one value" refers to. Does that mean one row? Or one name (which could be on multiple rows)?
I would be inclined to phrase this as:
SELECT (CASE WHEN COUNT(*) = 0 THEN 'None'
WHEN MIN(Name) = MAX(Name) THEN MIN(Name)
ELSE 'Multiple'
END) as one_row_value
FROM Users
WHERE Age < 20;
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
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%2f53347830%2fhow-can-i-return-different-value-based-on-the-selected-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use something like CASE in SELECT section to modify your returned result
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
add a comment |
You can use something like CASE in SELECT section to modify your returned result
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
add a comment |
You can use something like CASE in SELECT section to modify your returned result
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
You can use something like CASE in SELECT section to modify your returned result
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
answered Nov 17 '18 at 3:10
nguyentaijsnguyentaijs
1008
1008
add a comment |
add a comment |
The following query can help you out with the 3 conditions.
Instead of an empty string i put in 'None' for sake of clarity. You may edit it to be an empty string..
select case when count(*)=0 then 'None'
when count(name)=1 then max(name)
when count(name)>1 then max('Multiple')
end as name
from users1
where age <20
See a full demo with all the 3 test cases being catered to
- No records with age <20
- Exactly one record with age < 20
- More than one record with age <20
Demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=929d6ffca27ab5c4c05eeabdb6dd8b12
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
add a comment |
The following query can help you out with the 3 conditions.
Instead of an empty string i put in 'None' for sake of clarity. You may edit it to be an empty string..
select case when count(*)=0 then 'None'
when count(name)=1 then max(name)
when count(name)>1 then max('Multiple')
end as name
from users1
where age <20
See a full demo with all the 3 test cases being catered to
- No records with age <20
- Exactly one record with age < 20
- More than one record with age <20
Demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=929d6ffca27ab5c4c05eeabdb6dd8b12
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
add a comment |
The following query can help you out with the 3 conditions.
Instead of an empty string i put in 'None' for sake of clarity. You may edit it to be an empty string..
select case when count(*)=0 then 'None'
when count(name)=1 then max(name)
when count(name)>1 then max('Multiple')
end as name
from users1
where age <20
See a full demo with all the 3 test cases being catered to
- No records with age <20
- Exactly one record with age < 20
- More than one record with age <20
Demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=929d6ffca27ab5c4c05eeabdb6dd8b12
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
The following query can help you out with the 3 conditions.
Instead of an empty string i put in 'None' for sake of clarity. You may edit it to be an empty string..
select case when count(*)=0 then 'None'
when count(name)=1 then max(name)
when count(name)>1 then max('Multiple')
end as name
from users1
where age <20
See a full demo with all the 3 test cases being catered to
- No records with age <20
- Exactly one record with age < 20
- More than one record with age <20
Demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=929d6ffca27ab5c4c05eeabdb6dd8b12
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
answered Nov 17 '18 at 3:55
George JosephGeorge Joseph
1,590510
1,590510
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
add a comment |
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
Thanks a lot. Two more questions: 1. Why you use MAX here? Is it necessary? 2. If we call COUNT multiple times, will it be a performance issue?
– Jesse Zhang
Nov 17 '18 at 4:50
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
1. Any aggregated function that accepts <string_input> would do MIN is also fine. It is necessary to have that as we are grouping by the result set of (WHERE age<20) to get count()/count(name). 2. The easiest way to count records would be to make use of the COUNT keyword. Also sql is a declarative language, so we cannot assume that count() would be called multiple times here.
– George Joseph
Nov 17 '18 at 5:10
add a comment |
I am not sure what "one value" refers to. Does that mean one row? Or one name (which could be on multiple rows)?
I would be inclined to phrase this as:
SELECT (CASE WHEN COUNT(*) = 0 THEN 'None'
WHEN MIN(Name) = MAX(Name) THEN MIN(Name)
ELSE 'Multiple'
END) as one_row_value
FROM Users
WHERE Age < 20;
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
add a comment |
I am not sure what "one value" refers to. Does that mean one row? Or one name (which could be on multiple rows)?
I would be inclined to phrase this as:
SELECT (CASE WHEN COUNT(*) = 0 THEN 'None'
WHEN MIN(Name) = MAX(Name) THEN MIN(Name)
ELSE 'Multiple'
END) as one_row_value
FROM Users
WHERE Age < 20;
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
add a comment |
I am not sure what "one value" refers to. Does that mean one row? Or one name (which could be on multiple rows)?
I would be inclined to phrase this as:
SELECT (CASE WHEN COUNT(*) = 0 THEN 'None'
WHEN MIN(Name) = MAX(Name) THEN MIN(Name)
ELSE 'Multiple'
END) as one_row_value
FROM Users
WHERE Age < 20;
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
I am not sure what "one value" refers to. Does that mean one row? Or one name (which could be on multiple rows)?
I would be inclined to phrase this as:
SELECT (CASE WHEN COUNT(*) = 0 THEN 'None'
WHEN MIN(Name) = MAX(Name) THEN MIN(Name)
ELSE 'Multiple'
END) as one_row_value
FROM Users
WHERE Age < 20;
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
answered Nov 17 '18 at 13:34
Gordon LinoffGordon Linoff
800k37321426
800k37321426
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
add a comment |
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
Thanks for your answer, “one value” here means only one row is selected. I think both you and George are correct.
– Jesse Zhang
Nov 17 '18 at 13:57
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%2f53347830%2fhow-can-i-return-different-value-based-on-the-selected-results%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
