CODEWITHSUNDEEP
sqlsql-server
RookieCoder
RookieCoder

Reputation: 183

sort column by string in sql server?

LogID   Title   Message
1   Error Occured   Could not find stored procedure 'RT_SELECTAll1_Users'. : [UserID={2}],[UserSessionID={10068}]
2   Error Occured   A public action method 'LogsIndex' was not found on controller 'oMail.Web.Controllers.EmailTemplateController'. : [UserID={2}],[UserSessionID={20071}]
3   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]
4   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]
5   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]
6   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]
7   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]
8   Error Occured   The controller for path '/ControllerName/ActionName' was not found or does not implement IController. : [UserID={2}],[UserSessionID={20071}]

this is table in which error messages are stored.

is it possible to get count of all error message.

for e.g,

Message:- 'LogsIndex' was not. Count = 1

Message:- 'The controller for path' was not. Count = 5

Upvotes: 2

Views: 64

Answers (3)

Anthony Horne
Anthony Horne

Reputation: 2522

Since the messages are not exactly the same, I can only see you using LIKE and case:

SELECT
    CASE 
        WHEN Message like '%LogsIndex%' THEN 'LogsIndex'
        WHEN Message like 'The controller for path'' was not%' THEN 'Controller Path'
        ELSE 'Other'
    END AS UserMessage
    ,count(*)
FROM
    TableName
Group by
    CASE 
        WHEN Message like '%LogsIndex%' THEN 'LogsIndex'
        WHEN Message like 'The controller for path' was not%' THEN 'Controller Path'
        ELSE 'Other'
    END

You could make it a bit more elegant using CTE or maybe a UNION if you want to simply it.

You would want to limit this by date or something.

Upvotes: 1

dbajtr
dbajtr

Reputation: 2044

You select the message and perform a count then group the results by the message, something like this:

    Select Message, Count(*) as 'Count'
    From Table
    Group by Message

Upvotes: 1

Bhavika Zimbar
Bhavika Zimbar

Reputation: 481

Please try this answer.

SELECT Message, count(1) as MCount 
FROM TableName

Upvotes: 0

Related Questions