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 2000 Forums
 Transact-SQL (2000)
 Cursors or Not for counting?

Author  Topic 

cornall
Posting Yak Master

148 Posts

Posted - 2008-06-17 : 05:59:13
Hi,

In simple terms I have a table

computerName, errorType, dateLogged

I want to get a count of No of errorType = 1 in the last 7 days for each different computerName.

So I end up with


CompName NoOfErrors

ComputerA 27
ComputerB 76
ComputerC 2


I would do this using


SELECT COUNT(*) FROM table
WHERE computerName='ExampleComputer'
AND errorType = 1
AND dateLogged > DATEADD(day,-7,getDate())


Great...

Now my temptation is to load the list of computer names into a cursor and loop through replacing 'ExampleComputer' with my cursor output.

Am I mad to rush in with a cursor is there a nicer more efficient and better way to do this without a cursor. I can't think of one but I hope it is my lack of knowladge I just have a gut feeling that a cursor is wrong in this instance!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-17 : 06:02:52
If you want to get the error count per computername, just use:

SELECT Computername, COUNT(*) FROM table
WHERE errorType = 1
AND dateLogged > DATEADD(day,-7,getDate())
group by Computername
Go to Top of Page

cornall
Posting Yak Master

148 Posts

Posted - 2008-06-17 : 06:08:24
Exactly what I need. I knew there was a way to do it in SQL without nasty Cursors. Thank you. I need to brush up on my GROUP BY clause!

Cheers D
Go to Top of Page
   

- Advertisement -