Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Row count multiple tables within another query? 1

Status
Not open for further replies.

peterlyttle

Technical User
Joined
Nov 6, 2006
Messages
139
Location
GB
Hello,

Im pretty new to all this and would appreciate some help on this.

Code:
Select SMA.name,SMS.priority, SMA.pub_range, SMA.range, SMA.threshold
FROM [CFSP].[dbo].[MSmerge_identity_range] as MSIR
join [CFSP].[dbo].[sysmergesubscriptions] as SMS 
on SMS.subid = MSIR.subid
join [CFSP].[dbo].[sysmergearticles] as SMA
on SMA.artid = MSIR.artid

This returns the following -

Code:
name	priority pub_range range  threshold
Table1	75	10000000	1000	80
Table1	75	10000000	1000	80
Table2	75	10000000	1000	80
Table2	75	10000000	1000	80
Table3	75	10000000	1000	80
Table3	75	10000000	1000	80
Table4	75	10000000	1000	80
Table4	75	10000000	1000	80
Table5	75	10000000	1000	80
Table5	75	10000000	1000	80

I would like to add another column to the above query that does a count on each value returned in the name, eg Table1 has 2000 rows then the 2000 would be displayed alongside the priority etc.

Anyone any ideas?
 
It depends on your SQL Server version.

In SQL Server 2005+ you can use
Code:
Select SMA.name,SMS.priority, SMA.pub_range, SMA.range, SMA.threshold, count(SMA.Name) OVER (Partition by SMA.Name) as NamesCount
FROM [CFSP].[dbo].[MSmerge_identity_range] as MSIR
join [CFSP].[dbo].[sysmergesubscriptions] as SMS 
on SMS.subid = MSIR.subid
join [CFSP].[dbo].[sysmergearticles] as SMA
on SMA.artid = MSIR.artid

In prior versions of SQL Server you will do
Code:
Select SMA.name,SMS.priority, SMA.pub_range, SMA.range, SMA.threshold, cnt.NameCount
FROM [CFSP].[dbo].[MSmerge_identity_range] as MSIR
join [CFSP].[dbo].[sysmergesubscriptions] as SMS 
on SMS.subid = MSIR.subid
join [CFSP].[dbo].[sysmergearticles] as SMA
on SMA.artid = MSIR.artid
inner join (select SMA.Name, count(*) as NamesCount from SMA 
group by SMA.Name) cnt on SMA.Name = cnt.Name

(It may be required to slightly adjust the derived table, though, depending on your requirements).

PluralSight Learning Library
 
Hi, its SQL 2008 im using. Sorry I might not have explained myself correctly.

If I was to do the following -

Code:
SELECT COUNT(*) from Table1

on each line where Table1 would be replaced each time by the current value of the SMA.name (for table1, 2000)

eg
Code:
name row_count priority pub_range range  threshold
Table1  2000    75    10000000    1000    80
Table1  2000    75    10000000    1000    80
Table2  1245    75    10000000    1000    80
Table2  1245    75    10000000    1000    80
Table3  9924    75    10000000    1000    80
Table3  9924    75    10000000    1000    80
Table4  1111    75    10000000    1000    80
Table4  1111    75    10000000    1000    80
Table5  2002    75    10000000    1000    80
Table5  2002    75    10000000    1000    80
 
so when you're saying count of table1, table2... you actually mean counting the rows from those distinct tables, then brining them back into this query?

if that's what you mean, then you can't do it directly through t-sql.

It's possible to do it through dynamic sql, or build a stored procedure to scan and store counts for each table, then return the values.

however, if these are tables in your database, or another sql server database you can link to, then you can use the column row_count in the sys.dm_db_partition_stats dmv to bring back the rows, you may need to use sys.tables to get the tables names to link on as well...

--------------------
Procrastinate Now!
 
Ah, thanks for pointing me in the direction of that dm_db_partition_stats table I got it to work with that.

Code:
SELECT SMA.name,SMA.pub_range, SMA.range, SMA.threshold, SPS.row_count, SMS.db_name,SMS.subscriber_server,SMS.subid
FROM [CFSP].[dbo].[MSmerge_identity_range] AS MSIR
join [CFSP].[dbo].[sysmergesubscriptions] AS SMS 
ON SMS.subid = MSIR.subid
join [CFSP].[dbo].[sysmergearticles] AS SMA
ON SMA.artid = MSIR.artid
join sys.tables AS ST
ON ST.name = SMA.name
join sys.dm_db_partition_stats as SPS
ON SPS.object_id = ST.object_id
order by SMA.name asc

Cheers!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top