2

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

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
softwarematter
  • 26,673
  • 61
  • 165
  • 257

4 Answers4

3

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.

Community
  • 1
  • 1
jpw
  • 43,521
  • 6
  • 61
  • 82
2

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

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
1

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

ub3rst4r
  • 2,346
  • 24
  • 33
0

Consider the substring() function:

SELECT substring(TableName.Error,1,14) AS Category, 
       Count(*) As ErrorCount
FROM TableName
GROUP BY substring(TableName.Error,1,14)
Parfait
  • 97,543
  • 17
  • 91
  • 116