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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count rows that match condition
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 04/21/2008 :  21:52:25  Show Profile  Reply with Quote
Not sure how to do this but here is example of what I have

Table A
ID data1 data2 data3 data4
1 535 452 213 554
2 325 651 321 554
3 654 846 096 355
4 765 658 321 422

I want to have a select that will pull the following information out with count = the number of rows that have matching data in data4

ID data1 count
1 535 2
2 325 2
3 654 1
4 765 1

Right now I am using a VB script to loop thru get the current data4 value then using SELECT COUNT(data1) AS count FROM tbl_toolerrors WHERE data4 = {data4 value currently looking at}

Of course this take a bunch of trips to database and I think there should be a way to do it. I was thinking of a nested SQL querry like

Select data1, data2, ID, data3, (select count ...) Order by data1

can anyone help?

Starting Member

2 Posts

Posted - 04/21/2008 :  22:10:14  Show Profile  Reply with Quote
Nevermind I think I have it figured out by making views, but if anyone wants to jump in with some advice feel free.

Go to Top of Page

Aged Yak Warrior

940 Posts

Posted - 04/22/2008 :  01:16:45  Show Profile  Reply with Quote

select id, data1, (select count(*) from x as x1 where x1.data4=x.data4)
from x

Whatever your view is won't make a difference. A view is just a select statement.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000