Counting query. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Counting query.

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2004-01-26 : 14:11:23

Hi :)

I've ran into this problem.. that I want to count the number of records in table2 based on the id number from table1 and then finally update table1 with the right number of records

ex.

table1
id
countresult
text

table2
id
table1_id
text

I've tried with the GROUP BY clause, which counts the records correctly.. I know that I somehow must use the where clause, but I can't figure out how, since i don't know the id number of table1 in advance :(

SELECT COUNT(*) FROM table2 GROUP BY table1_id

I hope that some of you can help..

Best regards
Taz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:15:53

You have to JOIN to the other table. Something like this:

SELECT COUNT(*)
FROM table2 t2
INNER JOIN table1 ON t2.table1_id = t1.id
GROUP BY t2.table1_id

Tara

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-26 : 14:17:49

delete from table1

insert into table1 (id, countresult)
select table1id, count(*) from table2 group by table1id

That wount work, nevermind

TazMania
Yak Posting Veteran

63 Posts

Posted - 2004-01-27 : 16:13:51

Thx for the reply to both of you, but how do I put the values in table1.countresult accordingly ?

Best regards
Taz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 16:17:32

Please explain what you mean with an example.

Is this close? If it is, why would you want to do this? You shouldn't store a value like this. You should retrieve it as needed.


UPDATE Table1
SET countresult = 
	(SELECT COUNT(*) 
	FROM table2 t2
	INNER JOIN table1 ON t2.table1_id = t1.id
	GROUP BY t2.table1_id)

Tara

TazMania
Yak Posting Veteran

63 Posts

Posted - 2004-01-27 : 17:05:29

Hi Tara

Thanks, But i've tried that and the only error I got in query Analyzer where 'Subquery returned more than 1 value'

Regarding storeing the value, yes you are correct.. i've tried this first without what i've described earlier, but I thought it would be even more "crazy" to first loop through an ordinary sql query in my asp code and then inside this loop count the records, only so that I could keep the recordsets variable.

Oki what i'm looking for is.
ex.
table1
id
countresult
text

table2
id
table1_id
text

table1.countresult should consist of the number of records from table2.table1_id

lets say that table1 have two records, with unique id's.
In table2 there's f.ex. three records, but two of them have the same id number from table1 (table2.table1_id) back in table1 table1.countresult should in record one consist of the number two, and record two in table1.countresult should consist of the number one.

Here's an output of my asp code :

<%
SQL = " SELECT ServerID,ServerNavn,ServerLogCount FROM SERVERLIST ORDER BY ServerNavn ASC "
SET RS = Conn.Execute(SQL)

DO WHILE NOT RS.EOF

Response.Write RS("ServerNavn")&"-"
Response.Write RS("ServerLogCount")

RS.MoveNext
Loop
%>

hope that helped.

Best regards
Taz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:11:18

Could you explain with data?

Why don't you just get the rows returned from the stored procedure? Any query that you execute, it will tell you how many rows were affected as long as you don't run SET NOCOUNT ON.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:12:44

Or use SELECT @@ROWCOUNT

Tara

TazMania
Yak Posting Veteran

63 Posts

Posted - 2004-01-27 : 17:43:04

Wow you'r fast :) thx..

With data :

Serverlist :
id servername ServerLogCount
30 od_serv_print 2
31 od_serv_sql 1

ServerLog :
id serverlist.id problem
10 30 test1
11 30 test2
12 31 test3

The problem is, when lets say there will 10 times as much records in ServerList and 20 times as much records in ServerLog, because i can't predict the id number in ServerList, therefor when I wan't to run a count(*) and update the result to ServerList.ServerLogCount I must be sure that it's the correct number of records which will be placed in the right column.

But Tara, the way you've explained with select @@rowcount and stored procedure, wouldn't that be a problem since you wouldn't know the id number in advance which properly will result in the entire table/column updating with the same number of records ?

Best regards
Taz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:46:50

But you can predict the id number of Serverlist.

INSERT INTO ServerLogCount (servername, serverlogcount)
SELECT 'TDUGGAN', 1

SELECT @@IDENTITY

@@IDENTITY returns the identity value used in the previous insert statement.

Tara

TazMania
Yak Posting Veteran

63 Posts

Posted - 2004-01-28 : 05:20:05

Hi again,

Great thx, I didn't know that :)
Off to my codebench. thx Tara :)

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources