How to group on part of a column in PostgreSQL?

Question

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 ':'.

Answer 1

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.

Answer 2

split_part() seems simplest (as @ub3rst4r mentioned):

• Cut string after first occurrence of a character

But you don't need a subquery:

SELECT split_part(error, ':', 1) AS category, count(*) AS errorcount 
FROM   tbl
GROUP  BY 1;

And count(*) is slightly faster than count(<expression>).

GROUP BY 1 is a positional reference to the first SELECT item and a convenient shorthand for longer expressions. Example:

• Select first row in each GROUP BY group?

Answer 3

Here's what I came up with using a subquery and the split_part function:

SELECT *, COUNT(ErrorSplit) 
FROM (
  SELECT split_part(Error, ':', 1) AS ErrorSplit
  FROM tableA
) AS tableSplit
GROUP BY ErrorSplit;

Output:

   errorsplit  | count
----------------------
MajorCategoryX |  2
MajorCategoryY |  1

SQL Fiddle

Answer 4

Consider the substring() function:

SELECT substring(TableName.Error,1,14) AS Category, 
       Count(*) As ErrorCount
FROM TableName
GROUP BY substring(TableName.Error,1,14)