Get Count with Distinct Rows - 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

General SQL Server Forums

New to SQL Server Programming

Get Count with Distinct Rows

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2009-11-02 : 11:00:05

Here is my table schema with some data:

------------------------------------------------------------
tblErrors
------------------------------------------------------------
ErrorID | ErrorType | ErrorOccurred
------------------------------------------------------------
1 | System.NullReferenceException | default.aspx
2 | System.NullReferenceException | default.aspx
3 | System.NullReferenceException | test.aspx
4 | System.ArgumentNullException | default.aspx
5 | System.IndexOutOfRangeException | default.aspx
6 | System.IndexOutOfRangeException | default.aspx
7 | System.IndexOutOfRangeException | test.aspx
8 | System.IndexOutOfRangeException | test.aspx

I have a grid that displays the errors, but I want them to be grouped by ErrorType and ErrorOccurred, as well as showing the number of times an error like that occurred. It should look like this:

--------------------------------------------------------
Error Type | Error Occurred | Count
--------------------------------------------------------
System.NullReferenceException | default.aspx | 2
System.NullReferenceException | test.aspx | 1
System.ArgumentNullException | default.aspx | 1
System.IndexOutOfRangeException | default.aspx | 2
System.IndexOutOfRangeException | test.aspx | 2

I am having trouble getting the count for each one though. Here is my current query:

SELECT DISTINCT ErrorType, ErrorOccurred
FROM tblErrors

How can I get the count?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-02 : 11:07:23

Don't use DISTINCT, use GROUP BY and include the aggregate.

select errorType, ErrorOccured, count(*) as [count] from tblErrors group by errorType, ErrorOccured

Be One with the Optimizer
TG

Apples
Posting Yak Master

146 Posts

Posted - 2009-11-02 : 11:29:52

That worked great, thank you!

Apples
Posting Yak Master

146 Posts

Posted - 2009-11-02 : 12:01:32

One more question...

Say that I add a Timestamp column to tblErrors.

How could I modify my query to get the most recent timestamp for each item in my grid?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources