Conditionally selecting two columns
Here's a Data in my spreadsheet:
colA colB
NYC USA
NYC USA
ROC USA
SEA USA
YVR CAN
YYZ CAN
LON UK
LON CAN
LON USA
How do I get the data for the following rule:
Get all the cities and countries, for which there is more than 1 unique combination of city+country. In other words, extract only those cities (along with respective countries), which have more than one country.
From the above data, I'm looking to get the following result:
LON UK
LON CAN
LON USA
because LON is the only one which occurs more than 1 once in different countries.
Tried COUNTIF, UNIQUE, QUERY and none of them produce the result I hope for, because I know I'm not using them correctly.
Thank you.
sql google-sheets google-sheets-query google-query-language
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
add a comment |
Here's a Data in my spreadsheet:
colA colB
NYC USA
NYC USA
ROC USA
SEA USA
YVR CAN
YYZ CAN
LON UK
LON CAN
LON USA
How do I get the data for the following rule:
Get all the cities and countries, for which there is more than 1 unique combination of city+country. In other words, extract only those cities (along with respective countries), which have more than one country.
From the above data, I'm looking to get the following result:
LON UK
LON CAN
LON USA
because LON is the only one which occurs more than 1 once in different countries.
Tried COUNTIF, UNIQUE, QUERY and none of them produce the result I hope for, because I know I'm not using them correctly.
Thank you.
sql google-sheets google-sheets-query google-query-language
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
add a comment |
Here's a Data in my spreadsheet:
colA colB
NYC USA
NYC USA
ROC USA
SEA USA
YVR CAN
YYZ CAN
LON UK
LON CAN
LON USA
How do I get the data for the following rule:
Get all the cities and countries, for which there is more than 1 unique combination of city+country. In other words, extract only those cities (along with respective countries), which have more than one country.
From the above data, I'm looking to get the following result:
LON UK
LON CAN
LON USA
because LON is the only one which occurs more than 1 once in different countries.
Tried COUNTIF, UNIQUE, QUERY and none of them produce the result I hope for, because I know I'm not using them correctly.
Thank you.
sql google-sheets google-sheets-query google-query-language
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
Here's a Data in my spreadsheet:
colA colB
NYC USA
NYC USA
ROC USA
SEA USA
YVR CAN
YYZ CAN
LON UK
LON CAN
LON USA
How do I get the data for the following rule:
Get all the cities and countries, for which there is more than 1 unique combination of city+country. In other words, extract only those cities (along with respective countries), which have more than one country.
From the above data, I'm looking to get the following result:
LON UK
LON CAN
LON USA
because LON is the only one which occurs more than 1 once in different countries.
Tried COUNTIF, UNIQUE, QUERY and none of them produce the result I hope for, because I know I'm not using them correctly.
Thank you.
sql google-sheets google-sheets-query google-query-language
sql google-sheets google-sheets-query google-query-language
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
edited Nov 18 '18 at 4:16
MatBailie
59.3k1475110
59.3k1475110
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
asked Nov 18 '18 at 2:43
FMFFFMFF
83042251
83042251
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
In SQL, you would get the list of such cities by doing:
select city
from t
group by city
having min(country) <> max(country);
However, if you want the original rows, I would instead use exists:
select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
add a comment |
You need to use QUERY function as
=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
QUERY(
QUERY(
Data!A1:B10,
"SELECT colA, count(distinct ColB) GROUP BY ColA"
),
"WHERE Col2 > 1")
)
where Col2 is case-sensitive and should be written as initcapped as this.
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
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%2f53357426%2fconditionally-selecting-two-columns%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
In SQL, you would get the list of such cities by doing:
select city
from t
group by city
having min(country) <> max(country);
However, if you want the original rows, I would instead use exists:
select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
add a comment |
In SQL, you would get the list of such cities by doing:
select city
from t
group by city
having min(country) <> max(country);
However, if you want the original rows, I would instead use exists:
select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
add a comment |
In SQL, you would get the list of such cities by doing:
select city
from t
group by city
having min(country) <> max(country);
However, if you want the original rows, I would instead use exists:
select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
In SQL, you would get the list of such cities by doing:
select city
from t
group by city
having min(country) <> max(country);
However, if you want the original rows, I would instead use exists:
select city, country
from t
where exists (select 1 from t t2 where t2.city = t.city and t2.country <> t.country);
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
answered Nov 18 '18 at 4:16
Gordon LinoffGordon Linoff
765k35297401
765k35297401
add a comment |
add a comment |
You need to use QUERY function as
=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
QUERY(
QUERY(
Data!A1:B10,
"SELECT colA, count(distinct ColB) GROUP BY ColA"
),
"WHERE Col2 > 1")
)
where Col2 is case-sensitive and should be written as initcapped as this.
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
add a comment |
You need to use QUERY function as
=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
QUERY(
QUERY(
Data!A1:B10,
"SELECT colA, count(distinct ColB) GROUP BY ColA"
),
"WHERE Col2 > 1")
)
where Col2 is case-sensitive and should be written as initcapped as this.
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
add a comment |
You need to use QUERY function as
=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
QUERY(
QUERY(
Data!A1:B10,
"SELECT colA, count(distinct ColB) GROUP BY ColA"
),
"WHERE Col2 > 1")
)
where Col2 is case-sensitive and should be written as initcapped as this.
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
You need to use QUERY function as
=QUERY("SELECT ColA, ColB WHERE Col1='colA'"
QUERY(
QUERY(
Data!A1:B10,
"SELECT colA, count(distinct ColB) GROUP BY ColA"
),
"WHERE Col2 > 1")
)
where Col2 is case-sensitive and should be written as initcapped as this.
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
edited Nov 18 '18 at 5:13
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
answered Nov 18 '18 at 4:10
Barbaros ÖzhanBarbaros Özhan
12.7k71532
12.7k71532
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%2f53357426%2fconditionally-selecting-two-columns%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
