Rafa Ayadi Rafa Ayadi - 6 months ago 9
SQL Question

How to get most frequent values in SQL where value like a variable?

I have an SQL table, in which I store my application logs. I have a column errors, in which I store values like this example

+------+--------+----------------------------------------------+
| id | name | error |
+------+--------+----------------------------------------------+
| 1 | john | Flushing folder error on folderid 456 |
| 2 | paul | Flushing folder error on folderid 440 |
| 3 | gary | Error connection has timed out on source 320|
| 4 | ade | Error connection has timed out on source 220|
| 5 | fred | Error connection has timed out on source 821|
| 6 | bob | Reading errors occured on folder 400 |
| 7 | ade | Error connection has timed out on source 320|
| 8 | fred | Error connection has timed out on source 320|
| 9 | bob | Reading errors occured on folder 402 |
| 10 | ade | Error connection has timed out on source 320|
| 11 | fred | Error connection has timed out on source 320|
| 12 | bob | Reading errors occured on folder 400 |
| 13 | paul | Flushing folder error on folderid 100 |
+------+--------+----------------------------------------------+


The result I would like to get is something like :

+-------------------------------------------+------------+
| Error Like | Occurence |
+-------------------------------------------+------------+
| Error connection has timed out on source | 7 |
| Flushing folder error on folderid | 3 |
|Reading errors occured on folder | 3 |
+-------------------------------------------+------------+


Is there anyway I could do that in SQL? Errors are variables and I don't have the exhaustive list of all possible errors.

Thanks!

Answer

Remove the last digit part and count.

Query

SELECT LEFT(error, LEN(error) - 4) AS [Error Like], 
COUNT(LEFT(error, LEN(error) - 4)) AS [Occurence]
FROM tbl_error
GROUP BY LEFT(error, LEN(error) - 4);

Or you can do it with a sub-query also.

Query

SELECT t.[Error Like], COUNT(t.[Error Like]) AS [Occurence] FROM(
    SELECT LEFT(error, LEN(error) - 4) AS [Error Like]
    FROM tbl_error
)t
GROUP BY t.[Error Like];

If you are not aware about the last digit part, then

Query

SELECT t.[Error Like], COUNT(t.[Error Like]) as [Occurence] FROM(
    SELECT LEFT(error, LEN(error) - CHARINDEX(' ', REVERSE(error), 1)) AS [Error Like]
    FROM tbl_error
)t
GROUP BY t.[Error Like]
ORDER BY COUNT(t.[Error Like]) desc, t.[Error Like];

Result

+--------------------------------------------+-----------+
| Error Like                                 | Occurence | 
+--------------------------------------------+-----------+
| Error connection has timed out on source   | 7         | 
| Flushing folder error on folderid          | 3         | 
| Reading errors occured on folder           | 3         | 
+--------------------------------------------+-----------+

Find a demo here