SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

folkes
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?

folkes
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.

Thanks,
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 04/22/2008 :  01:16:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000