Group identical error message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group identical error message

Author

Topic

ashaked
Starting Member

4 Posts

Posted - 2012-04-04 : 17:00:16

Hi all,
I hope I'm writing in the right place.
I'm trying to merge identical error message fields that I receive in the same time stamp.
Consider the following example to demonstrate my problem:

timeLogged Error Message
10:42:12 pm not valid
10:42:12 pm could not reach
10:42:14 pm could not find
10:42:15 pm could not find
10:42:15 pm could not find
10:42:15 pm could not find
10:42:16 pm could not find
10:42:17 pm could not find
10:42:22 pm could not reach

I want all the "could not find" rows to be shown as one row with the first time the error logged (i.e. - the first timeLogged value) and the number (count) of that values.
Any suggestion?
Thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-04 : 17:18:16

You can do that with:

SELECT timeLogged, ErrorMessage FROM myTable
GROUP BY timeLogged, ErrorMessage

or:

SELECT DISTINCT timeLogged, ErrorMessage FROM myTable

I have a feeling that's not what you're asking for, but try those first just to make sure.

ashaked
Starting Member

4 Posts

Posted - 2012-04-04 : 17:33:26

Hi Rob,
Thanks for the answer but this is not what I'm looking for.
The first query you wrote is a good start but I want to merge the sequential rows that have the same error message and count them.
For my example in the initial message the result would be:


timeLogged  Error Message      Count
10:42:12 pm not valid          1   
10:42:12 pm could not reach    1
10:42:14 pm could not find     6
10:42:22 pm could not reach    1

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-04 : 18:04:40

Oooops, should've read the first one more closely:

SELECT timeLogged, ErrorMessage, COUNT(*) count
FROM myTable
GROUP BY timeLogged, ErrorMessage

ashaked
Starting Member

4 Posts

Posted - 2012-04-04 : 18:35:45

This is almost the right answer but the sequential rows are not merged.
I'm trying to merge a group of identical error message in the same timeLogged period and count them.
Take a look at my example. :)

ashaked
Starting Member

4 Posts

Posted - 2012-04-05 : 12:54:02

Hi all,
Since I did not found the right SQL query for what I'm looking for,
I did it in two stages:
First one was the simple SQL query with no merge and the second one is merging the relevant rows in the code behind.
Thanks,
ashaked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources