SQL Sum with Sub Query?

Question

Thanks for any help in advance, I can't wrap my SQL skills around this ... I have two tables:

Settings

Customerid ViewerLimit
; fifty
; fifty

Distribution

Customerid ServerIP
>> p>

I want to calculate the load on each server. A client shares the load if they have more than one server, so here client 1 puts a load of 25 on each server. The result I'm trying to get is this:

Serverip load
stream3 75
stream4 25

I tried to make the same sum function:

sum(viewerlimit/(count # of servers)) as load group by serverip 

But I cannot execute a sub-request in the sum function. There are many clients, and possibly many servers for each client, so it will become too complex to perform manually. I appreciate any input.

Answer 1

The following is a non-inverse version with an invoice in the view:

 select serverip, sum (viewerlimit/cast (ServerCount as float)) Load from ( select customerid, count(*) ServerCount from distribution group by customerid ) a inner join settings on a.customerid = settings.customerid inner join distribution on settings.customerid = distribution.customerid group by serverip 

Sql Fiddle to play

UPDATE - attempt to explain

Derived tables are used to create custom result sets that can be combined with the main part of the query. It is placed from the sentence and is enclosed in parentheses. You can use everything that ordinary choices, top, order, aggregate functions, etc. can use. The only thing you cannot use is correlation with the table in the main body. Oh, and CTE. The view must be an alias.

In the previous test table "a", the number of servers by customerid is selected. The main body sees it as a table with CustomerId and ServerCount columns, ready for use as any column from all the tables listed. Joining to customerid is done between the settings and "a". Since this ratio is 1: 1 (both tables produce one row, taking into account customerid), duplication does not occur.

Answer 2

How do I count the servers in a subquery and assign it to a query variable, and then use this query variable inside the sum function?

Answer 3

 select d.serverip, sum(s.viewerlimit/d.devider) from ( select customerid, serverip, COUNT(serverip) over (partition by customerid) servercount from distribution ) d join settings s on s.customerid=d.customerid group by d.serverip