Find rows where column value for entire group is 'P'
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have table "Camera", I need to find the camera's who has only used Camera_mode 'P' and no other mode.
Table structure
Cam_serial_no Camera_mode
abc123 P
abc123 B
abc123 V
pqr111 P
xyz101 P
xyz101 V
xyz101 B
I could get desired output using below query, But I was wondering if there is any easy way
select *
from (select Cam_serial_no, collect_set(camera_mode) as val
from camera
group by Cam_serial_no
) a
where size(val)=1 and val[0] = 'P';
desired output is
pqr111 P
mysql sql
edited Jan 3 at 21:14
Salman A
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
add a comment |
0
I have table "Camera", I need to find the camera's who has only used Camera_mode 'P' and no other mode.
Table structure
Cam_serial_no Camera_mode
abc123 P
abc123 B
abc123 V
pqr111 P
xyz101 P
xyz101 V
xyz101 B
I could get desired output using below query, But I was wondering if there is any easy way
select *
from (select Cam_serial_no, collect_set(camera_mode) as val
from camera
group by Cam_serial_no
) a
where size(val)=1 and val[0] = 'P';
desired output is
pqr111 P
mysql sql
edited Jan 3 at 21:14
Salman A
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
You are looking for theHAVINGclause. See juergen d's answer on that. There are many ways to check the values; e.g.having min(camera_mode) = 'P' and max(camera_mode) = 'P'.
– Thorsten Kettner
Jan 3 at 20:11
add a comment |
0
0
0
I have table "Camera", I need to find the camera's who has only used Camera_mode 'P' and no other mode.
Table structure
Cam_serial_no Camera_mode
abc123 P
abc123 B
abc123 V
pqr111 P
xyz101 P
xyz101 V
xyz101 B
I could get desired output using below query, But I was wondering if there is any easy way
select *
from (select Cam_serial_no, collect_set(camera_mode) as val
from camera
group by Cam_serial_no
) a
where size(val)=1 and val[0] = 'P';
desired output is
pqr111 P
mysql sql
edited Jan 3 at 21:14
Salman A
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
I have table "Camera", I need to find the camera's who has only used Camera_mode 'P' and no other mode.
Table structure
Cam_serial_no Camera_mode
abc123 P
abc123 B
abc123 V
pqr111 P
xyz101 P
xyz101 V
xyz101 B
I could get desired output using below query, But I was wondering if there is any easy way
select *
from (select Cam_serial_no, collect_set(camera_mode) as val
from camera
group by Cam_serial_no
) a
where size(val)=1 and val[0] = 'P';
desired output is
pqr111 P
mysql sql
mysql sql
edited Jan 3 at 21:14
Salman A
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
edited Jan 3 at 21:14
Salman A
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
edited Jan 3 at 21:14
Salman A
185k67346441
edited Jan 3 at 21:14
Salman A
185k67346441
edited Jan 3 at 21:14
Salman A
185k67346441
185k67346441
asked Jan 3 at 20:05
user2232840user2232840
64
asked Jan 3 at 20:05
user2232840user2232840
64
asked Jan 3 at 20:05
user2232840user2232840
64
64
You are looking for theHAVINGclause. See juergen d's answer on that. There are many ways to check the values; e.g.having min(camera_mode) = 'P' and max(camera_mode) = 'P'.
– Thorsten Kettner
Jan 3 at 20:11
add a comment |
You are looking for theHAVINGclause. See juergen d's answer on that. There are many ways to check the values; e.g.having min(camera_mode) = 'P' and max(camera_mode) = 'P'.
– Thorsten Kettner
Jan 3 at 20:11
You are looking for the
HAVING clause. See juergen d's answer on that. There are many ways to check the values; e.g. having min(camera_mode) = 'P' and max(camera_mode) = 'P'.– Thorsten Kettner
Jan 3 at 20:11
You are looking for the
HAVING clause. See juergen d's answer on that. There are many ways to check the values; e.g. having min(camera_mode) = 'P' and max(camera_mode) = 'P'.– Thorsten Kettner
Jan 3 at 20:11
add a comment |
1 Answer
1
active
oldest
votes
2
Group by the Cam_serial_no and take only those having no other mode than P
select Cam_serial_no
from camera
group by Cam_serial_no
having sum(Camera_mode <> 'P') = 0
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0
– juergen d
Jan 7 at 16:11
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
});
}
});
draft saved
draft discarded
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%2f54029062%2ffind-rows-where-column-value-for-entire-group-is-p%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
2
Group by the Cam_serial_no and take only those having no other mode than P
select Cam_serial_no
from camera
group by Cam_serial_no
having sum(Camera_mode <> 'P') = 0
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0
– juergen d
Jan 7 at 16:11
add a comment |
2
Group by the Cam_serial_no and take only those having no other mode than P
select Cam_serial_no
from camera
group by Cam_serial_no
having sum(Camera_mode <> 'P') = 0
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0
– juergen d
Jan 7 at 16:11
add a comment |
2
2
2
Group by the Cam_serial_no and take only those having no other mode than P
select Cam_serial_no
from camera
group by Cam_serial_no
having sum(Camera_mode <> 'P') = 0
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
Group by the Cam_serial_no and take only those having no other mode than P
select Cam_serial_no
from camera
group by Cam_serial_no
having sum(Camera_mode <> 'P') = 0
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
answered Jan 3 at 20:07
juergen djuergen d
162k24211270
162k24211270
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0
– juergen d
Jan 7 at 16:11
add a comment |
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0
– juergen d
Jan 7 at 16:11
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Hi Juergen, I am getting below error when i run your query in Hive; FAILED: UDFArgumentTypeException Only numeric or string type arguments are accepted but boolean is passed.
– user2232840
Jan 7 at 16:09
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:
having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0– juergen d
Jan 7 at 16:11
Because MySQL != Hive. The syntax is different. This should work for almost all DB engines:
having sum(case when Camera_mode <> 'P' then 1 else 0 end) = 0– juergen d
Jan 7 at 16:11
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54029062%2ffind-rows-where-column-value-for-entire-group-is-p%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 are looking for the
HAVINGclause. See juergen d's answer on that. There are many ways to check the values; e.g.having min(camera_mode) = 'P' and max(camera_mode) = 'P'.– Thorsten Kettner
Jan 3 at 20:11