Group By Except For Certain Value

Question

I'm trying to create a (sqlite) query that will perform a GROUP BY but will not group anything with the value 'unknown'. For example, I have the 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 proper query with 'GROUP BY name, parent_id, school_id' I need the following rows returned:

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

As a single query...

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 possibly...

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 a slightly increased complexity GROUP BY.

Answer 2

You can't easily do this with one statement but you can UNION the results of two statements

• GROUP the list of all but unknown
• Add (UNION) the 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'

Note that I assume you have posted wrong unknown id's in your result