devnull devnull - 7 months ago 19
SQL Question

How to group on part of a column in PostgreSQL?

I have the following table

tableA
in PostgreSQL:

+-------------+-------------------------+
| OperationId | Error |
+-------------+-------------------------+
| 1 | MajorCategoryX:DetailsP |
| 2 | MajorCategoryX:DetailsQ |
| 3 | MajorCategoryY:DetailsR |
+-------------+-------------------------+


How do I group the MajorErrorCategory such that I get the following?

+----------------+------------+
| Category | ErrorCount |
+----------------+------------+
| MajorCategoryX | 2 |
| MajorCategoryY | 1 |
+----------------+------------+


Category
is the first part of
Error
after splitting on ':'.

jpw jpw
Answer

Assuming the length before the : can vary you could use substring in combination with strpos to achieve your results:

SELECT 
    SUBSTRING(error, 0, STRPOS(error, ':')) AS Category,     
    COUNT(*) AS ErrorCount
FROM t
GROUP BY SUBSTRING(error, 0, STRPOS(error, ':'))

Sample SQL Fiddle

If you don't want to repeat the function calls you could of course wrap that part in a suquery or common table expression.

Comments