sqlite group by ... having ..... (then select all of them)
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
sqlite group by ... having ..... (then select all of them)
Hya,
I am using sqlite.
I would like to have
Code:
1 dog
1 cow
3 pig
3 horse
out of
Code:
1 dog
1 cow
1 apple
3 pig
3 horse
5 orange
8 peach
9 panda
The 1st column is ID, 2nd is whatever. I want to select all ID's associated to more than two animals and all of them.
I do not need red lines, (not animal). I do not need "9 panda", because it is associated to only one animal.
Following what I did.
Code:
select ID, whatever form table
where whatever in (dog, cow, pig, horse, panda)
group by ID
having count(*) >1
But what I get looks like
Code:
1 dog
3 pig
My textbook of general SQL says "it is not allowed to select "non-group by" column. (in this case "whatever") Also, this web page for mySQL reads something similar and history.
I can go back to the table and select with ID. but "1 apple" shows up, so additional step(s) is necessary. (error prone and waste of resources)
1.Have another table say ANIMALS which would have only the animal names you need to choose in a field called ANIMAL_NAME as records(dog, cow, pig, horse, panda)
2. Essentially enhancement of NEVEMTEME's solution in post #2.
Quote:
select t1.* from table t1 where
t1.ID in (select t2.ID from table t2, animals where t2.whatever=animal_name group by t2.ID having count(*) >1)
Because the data model (i.e. table structure) does not support the desired query.
For one thing, what is an "animal"? The model does not know so you must tell it - for two purposes - count and final select... messy.
Also, although you can name a column anything you like, ID is generally a unique identifier, but it is not so in your table. There is no unique identifier in fact.
The model (such as it exists) is just a table, not relational data - look into "normalization".
A properly modeled and normalized relational data structure (as opposed to just a table) would make such a query very efficient and easy for huge data sets. SQL is a relational query language, not a table query language. Think in sets (i.e., relationally) in your model, and in your queries, and they will take on their natural power with ease!
Last edited by astrogeek; 10-17-2016 at 08:02 PM.
Reason: tpos, typs, typos
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.