Joining tables

[ironcurtain]

Hi,

I need to make a query some joins but I met some problem.
I have 2 tables connected many to one relationship.

Tables are Systems (SystemID, SystemName) and Servers (ServerID, ServerName, SystemID, RegionID) (one system can have many servers)
I need to use Servers table to make join with Region table bacause I want to count Systems in each region.

I tried to do this with joins but it doesn't work because I always get servers count.

Any ideas how it can be done?

[rmiao]

How does region table look like?

[ironcurtain]

The region table has 2 columns: regionID, regionName. This is dictionary table with 6 rows.

[skhanal]

Use group by RegionID and COUNT to get servers for each region.

[ironcurtain]

Thank You skhanal. I just figured it out by myself a few moments ago.

[ironcurtain]

I made some changes and now I am trying to use CTE and Over clause to count records.

I created below query byt it doesn't work. I got error:

Incorrect syntax near 'distinct'.

-- Define the CTE expression name and column list.
WITH Counter_CTE (SystemID, SystemName, ServerID, ServerName, SystemModel, SystemType, SystemGroup, RemsCode, City, Region)
AS
-- Define the CTE query.
(
select
sy.SystemID, sy.SystemName, se.ServerID, se.servername, smd.systemmodel, st.systemtype, sg.systemgroup, re.remscode, ci.city, co.region
from
Systems sy
inner join Servers se on sy.SystemID = se.systemid
inner join Systemmodels smd on sy.systemmodelid = smd.SystemModelID
inner join SystemTypes st on smd.SystemTypeID = st.SystemTypeID
inner join SystemGroups sg on st.SystemGroupID = sg.SystemGroupID
inner join REMS re on se.REMSID = re.REMSID
inner join Cities ci on re.CityID = ci.CityID
inner join Countries co on ci.CountryID = co.CountryID
--order by sy.SystemName
)
-- Define the outer query referencing the CTE name.
SELECT SystemID, region, COUNT(distinct SystemID) over (partition by systemid) AS TotalSsytems
FROM Counter_CTE
ORDER BY TotalSsytems desc
GO

I found that distnct can be used with count.
Any ideas how it should look like?