How do I select records from Sqlite table partially grouped?
0
I have a table like this. And I want to select Fruits grouped together based on the group_id but not the Vegetables and Nuts. For Vegetable and Nuts, I want them all.
group_id name type
-----------------------------------
1 Green Apple Fruit
1 Red Apple Fruit
1 Blue Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
This is how I have tried right now. This works well, but I want to know if there is any simpler approach.
select * from Grocessaries GROUP BY group_id HAVING type in ('Fruit', 'Drinks')
UNION all
select * from Grocessaries where type in ('Vegetable', 'Nuts')
Basically, I want the result something like this (grouped Fruits and all Vegetables and Nuts)
group_id name type
-----------------------------------
1 Green Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
sql sqlite group-by
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
|
show 2 more comments
0
I have a table like this. And I want to select Fruits grouped together based on the group_id but not the Vegetables and Nuts. For Vegetable and Nuts, I want them all.
group_id name type
-----------------------------------
1 Green Apple Fruit
1 Red Apple Fruit
1 Blue Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
This is how I have tried right now. This works well, but I want to know if there is any simpler approach.
select * from Grocessaries GROUP BY group_id HAVING type in ('Fruit', 'Drinks')
UNION all
select * from Grocessaries where type in ('Vegetable', 'Nuts')
Basically, I want the result something like this (grouped Fruits and all Vegetables and Nuts)
group_id name type
-----------------------------------
1 Green Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
sql sqlite group-by
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
1
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
2
In the first queryGROUP BY group_idmake no sence because you didn't use any aggregate function..
– D-Shih
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
WhyGreen Apple Fruitand notRed Apple FruitorBlue Apple Fruit?
– forpas
Nov 15 '18 at 11:39
|
show 2 more comments
0
0
0
I have a table like this. And I want to select Fruits grouped together based on the group_id but not the Vegetables and Nuts. For Vegetable and Nuts, I want them all.
group_id name type
-----------------------------------
1 Green Apple Fruit
1 Red Apple Fruit
1 Blue Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
This is how I have tried right now. This works well, but I want to know if there is any simpler approach.
select * from Grocessaries GROUP BY group_id HAVING type in ('Fruit', 'Drinks')
UNION all
select * from Grocessaries where type in ('Vegetable', 'Nuts')
Basically, I want the result something like this (grouped Fruits and all Vegetables and Nuts)
group_id name type
-----------------------------------
1 Green Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
sql sqlite group-by
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
I have a table like this. And I want to select Fruits grouped together based on the group_id but not the Vegetables and Nuts. For Vegetable and Nuts, I want them all.
group_id name type
-----------------------------------
1 Green Apple Fruit
1 Red Apple Fruit
1 Blue Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
This is how I have tried right now. This works well, but I want to know if there is any simpler approach.
select * from Grocessaries GROUP BY group_id HAVING type in ('Fruit', 'Drinks')
UNION all
select * from Grocessaries where type in ('Vegetable', 'Nuts')
Basically, I want the result something like this (grouped Fruits and all Vegetables and Nuts)
group_id name type
-----------------------------------
1 Green Apple Fruit
2 Green Peas Vegetable
2 Snow Peas Vegetable
2 Another Pea Vegetable
3 Ground Nut Nuts
3 Peanut Nuts
4 Carrot Vegetable
sql sqlite group-by
sql sqlite group-by
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
edited Nov 15 '18 at 11:36
Ramaraj T
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
asked Nov 13 '18 at 7:36
Ramaraj TRamaraj T
4,50222960
4,50222960
1
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
2
In the first queryGROUP BY group_idmake no sence because you didn't use any aggregate function..
– D-Shih
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
WhyGreen Apple Fruitand notRed Apple FruitorBlue Apple Fruit?
– forpas
Nov 15 '18 at 11:39
|
show 2 more comments
1
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
2
In the first queryGROUP BY group_idmake no sence because you didn't use any aggregate function..
– D-Shih
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
WhyGreen Apple Fruitand notRed Apple FruitorBlue Apple Fruit?
– forpas
Nov 15 '18 at 11:39
1
1
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
2
2
In the first query
GROUP BY group_id make no sence because you didn't use any aggregate function..– D-Shih
Nov 13 '18 at 15:58
In the first query
GROUP BY group_id make no sence because you didn't use any aggregate function..– D-Shih
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
Why
Green Apple Fruit and not Red Apple Fruit or Blue Apple Fruit?– forpas
Nov 15 '18 at 11:39
Why
Green Apple Fruit and not Red Apple Fruit or Blue Apple Fruit?– forpas
Nov 15 '18 at 11:39
|
show 2 more comments
1 Answer
1
active
oldest
votes
1
Since you're handling Fruits (and Drinks) specially in the UI the actual name returned for them isn't that important so you could do
SELECT group_id,
CASE type
WHEN 'Fruits' THEN 'Fruits' -- or whatever you want to display
WHEN 'Drinks' THEN 'Drinks'
ELSE name
END NameGrouped,
type
FROM Grocessaries
GROUP BY group_id, NameGrouped, type
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
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%2f53276008%2fhow-do-i-select-records-from-sqlite-table-partially-grouped%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
1
Since you're handling Fruits (and Drinks) specially in the UI the actual name returned for them isn't that important so you could do
SELECT group_id,
CASE type
WHEN 'Fruits' THEN 'Fruits' -- or whatever you want to display
WHEN 'Drinks' THEN 'Drinks'
ELSE name
END NameGrouped,
type
FROM Grocessaries
GROUP BY group_id, NameGrouped, type
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
add a comment |
1
Since you're handling Fruits (and Drinks) specially in the UI the actual name returned for them isn't that important so you could do
SELECT group_id,
CASE type
WHEN 'Fruits' THEN 'Fruits' -- or whatever you want to display
WHEN 'Drinks' THEN 'Drinks'
ELSE name
END NameGrouped,
type
FROM Grocessaries
GROUP BY group_id, NameGrouped, type
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
add a comment |
1
1
1
Since you're handling Fruits (and Drinks) specially in the UI the actual name returned for them isn't that important so you could do
SELECT group_id,
CASE type
WHEN 'Fruits' THEN 'Fruits' -- or whatever you want to display
WHEN 'Drinks' THEN 'Drinks'
ELSE name
END NameGrouped,
type
FROM Grocessaries
GROUP BY group_id, NameGrouped, type
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
Since you're handling Fruits (and Drinks) specially in the UI the actual name returned for them isn't that important so you could do
SELECT group_id,
CASE type
WHEN 'Fruits' THEN 'Fruits' -- or whatever you want to display
WHEN 'Drinks' THEN 'Drinks'
ELSE name
END NameGrouped,
type
FROM Grocessaries
GROUP BY group_id, NameGrouped, type
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
answered Nov 15 '18 at 14:41
Joakim DanielsonJoakim Danielson
7,6103724
7,6103724
add a comment |
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%2f53276008%2fhow-do-i-select-records-from-sqlite-table-partially-grouped%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
1
Can you elaborate on logic? Why Blue and Red apples were filtered out but Snow Peas stayed in your desired output?
– peterm
Nov 13 '18 at 15:57
2
In the first query
GROUP BY group_idmake no sence because you didn't use any aggregate function..– D-Shih
Nov 13 '18 at 15:58
It looks like you're mixing grouping and ordering
– peterm
Nov 13 '18 at 15:58
Order is not a problem for me. I've removed the ordering part from the query. I just care about the grouping part.
– Ramaraj T
Nov 15 '18 at 11:37
Why
Green Apple Fruitand notRed Apple FruitorBlue Apple Fruit?– forpas
Nov 15 '18 at 11:39