Group except for certain values

Question

I am trying to create a query (sqlite) that will execute GROUP BY but will not group anything with a value of "unknown". For example, I have a table:

id | name | parent_id | school_id | 1 | john | 1 | 1 | 2 | john | 1 | 1 | 3 | john | 1 | 1 | 4 | nick | 2 | 2 | 5 | nick | 2 | 2 | 6 | nick | 3 | 3 | 7 | bob | 4 | 4 | 8 | unknown | 5 | 5 | 9 | unknown | 5 | 5 | 10| unknown | 5 | 5 | 

With the correct query with 'GROUP BY name, parent_id, school_id', I need the following lines:

 id | name | parent_id | school_id | 1 | john | 1 | 1 | 3 | nick | 2 | 2 | 4 | nick | 3 | 3 | 5 | bob | 4 | 4 | 6 | unknown | 5 | 5 | 7 | unknown | 5 | 5 | 8 | unknown | 5 | 5 | 

Any help would be greatly appreciated. Thanks!

Answer 1

You cannot easily do this with a single statement, but you can UNION get the results of two statements

• GROUP list of all but unknown
• Add ( UNION ) a list of all unknown

SQL statement

 SELECT MIN(id), name, parent_id, school_id FROM YourTable WHERE name <> 'unknown' GROUP BY name, parent_id, school_id UNION ALL SELECT id, name, parent_id, school_id FROM YourTable WHERE name = 'unknown' 

Please note that I assume that you entered the wrong unknown id in your result

Answer 2

Like one request ...

 SELECT MIN(id) AS id, name, parent_id, school_id FROM yourTable GROUP BY CASE WHEN name = 'unknown' THEN id ELSE 0 END, name, parent_id, school_id 

Or maybe ...

 GROUP BY CASE WHEN name <> 'unknown' THEN name ELSE CAST(id AS VARCHAR(???)) END, parent_id, school_id -- Where VARCHAR(???) is the data type of the `name` field. -- Also assumes no value in `name` is the same as an id for an 'unknown' field 

Both avoid UNION and the overhead of parsing the table twice, replacing it with the slightly increased GROUP BY complexity.

Answer 3

 SELECT MIN(id), name, parent_id, school_id FROM Table WHERE name <> 'unknown' GROUP BY name, parent_id, school_id UNION ALL SELECT id, name, parent_id, school_id FROM Table WHERE name = 'unknown' 

Answer 4

 SELECT MIN(id) AS id, IF(tmpname=id,"unknown",tmpname) AS name, parent_id, school_id FROM ( SELECT id,parent_id,school_id IF(name="unknown",id,name) AS tmpname FROM <tablename> ) AS baseview GROUP BY tmpname