distinct records count, and what each contains

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 2005 Forums

Transact-SQL (2005)

distinct records count, and what each contains

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-01 : 15:08:52

I am trying to get the distinct error messages and how many times each occurs. What I have below is not working, I only get 1 as the count.

entry_data is a text field, thus the cast.

Any help?

SELECT DISTINCT CAST(entry_data AS varchar(MAX)) AS error_msg, COUNT(CAST(entry_data AS varchar(MAX))) AS count_of_error
FROM pos_log
WHERE (entry_data LIKE '%<MsgStatusCd>Error</MsgStatusCd>%')
GROUP BY CAST(entry_data AS varchar(MAX))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 15:16:44

Show us some sample data, perhaps 5-10 rows, and the expected output using that sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 15:23:43

What happens when you run the query again, without the DISTINCT keyword?

E 12°55'05.63"
N 56°04'39.26"

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-01 : 15:34:37

I have modified the query to limit the portion of the log being looked at to the relevant data.

Here is the new query:

SELECT DISTINCT
SUBSTRING(CAST(entry_data AS varchar(MAX)), PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15,
PATINDEX('%</MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) - (PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15))
AS Expr1, COUNT(SUBSTRING(CAST(entry_data AS varchar(MAX)), PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15,
PATINDEX('%</MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) - (PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15)))
AS countoferror
FROM pos_log
WHERE (entry_data LIKE '%<MsgStatusCd>Error</MsgStatusCd>%')
GROUP BY CAST(entry_data AS varchar(MAX))

(I tried to indent properly, hope that comes across)

I get 41 rows returned, each with a different error message, but the countoferror stays at 0.

To answer your question, if I remove the distinct keyword and run the query below, I get 209 records, still with 1 for countoferror:
SELECT SUBSTRING(CAST(entry_data AS varchar(MAX)), PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15,
PATINDEX('%</MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) - (PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15))
AS Expr1, COUNT(SUBSTRING(CAST(entry_data AS varchar(MAX)), PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15,
PATINDEX('%</MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) - (PATINDEX('%<MsgStatusDesc>%', CAST(entry_data AS varchar(MAX))) + 15)))
AS countoferror
FROM pos_log
WHERE (entry_data LIKE '%<MsgStatusCd>Error</MsgStatusCd>%')
GROUP BY CAST(entry_data AS varchar(MAX))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 15:43:16

Now when you are on SQL Server 2005, you can

1. Edit column to VARCHAR(MAX)
2. Edit column to XML

This way you don't have to clutter your code with a massive amount of CAST's.

E 12°55'05.63"
N 56°04'39.26"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 15:45:42

It's hard to help without providing sample data and expected output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources