sqlite group by ... having ..... (then select all of them)

kaz2100 · 09-28-2016, 09:48 PM

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)

How does everybody survive this situation?

Thanks in advance.

cheers

NevemTeve · 09-29-2016, 03:10 AM

Code:

select t1.* from table t1 where
t1.ID in (select t2.ID from table t2 ... group by t2.ID having count(*) >1)

kaz2100 · 09-29-2016, 09:53 PM

Thanks for your comment.

Yes, it is one of solutions. A little bit tweak is necessary to avoid "1 apple".

Most probably, to make a new table with "whatever in (animal names....)" then go to your trick is less error prone.

Here is another web site.

AnanthaP · 10-12-2016, 08:30 AM

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)

kaz2100 · 10-17-2016, 06:49 PM

Hya

Apology for delay.

Yes, your idea works.

cheers

(why is this NOT an easy task?)

astrogeek · 10-17-2016, 07:52 PM

Quote:

Originally Posted by kaz2100

(why is this NOT an easy task?)

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!

kaz2100 · 10-19-2016, 09:05 PM

Hya,

Thanks for your comment.

Yes, the definition of animals is omitted for simplicity.

I did not think of modifying (normalize) the original table. Yes, it makes sense.

cheers

P.S.
I was trying to fiddle with group_concat() and additional tweaks.