CODEWITHSUNDEEP
sqlsqlitegroup-by
Ramaraj T
Ramaraj T

Reputation: 5230

How do I select records from Sqlite table partially grouped?

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

Upvotes: 0

Views: 40

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 51892

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

Upvotes: 1

Related Questions