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
 Count rows that match condition

Author  Topic 

folkes
Starting Member

2 Posts

Posted - 2008-04-21 : 21:52:25
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?

folkes
Starting Member

2 Posts

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

Thanks,
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-22 : 01:16:45
Maybe:

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
   

- Advertisement -