CODEWITHSUNDEEP
sql
gbusman
gbusman

Reputation: 167

SQL Sum with Sub Query?

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

Settings

Customerid     ViewerLimit
       1                   50
       2                   50

Distribution

Customerid     ServerIP
       1                 stream3
       1                 stream4
       2                 stream3

I want to calculate the load on each server. A customer divides the load if they have more than one server, so here customer 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 do a sum function similar to this:

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

But I can't do a sub query within a sum function. There are many customers and possibly many servers per customer so it will become too complex to do manually. I appreciate any input.

Upvotes: 9

Views: 32332

Answers (3)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Here is uninspired version with count in derived table:

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 around

UPDATE - an attempt at explanation

Derived tables are used to produce ad-hoc result sets that can be joined to main body of a query. It is placed in from clause and enclosed in parenthesis. You can use anything an ordinary select can use, top, order by, aggregate functions etc. The only thing you cannot use is correlation to a table in main body. Oh, and CTE. Derived table must be aliased.

In previous sample derived table 'a' selects counts of servers by customerid. Main body sees it as a table with CustomerId and ServerCount columns, ready to be used as any column from all listed tables. A join on customerid is performed between settings and 'a'. As this is 1:1 relation (both tables produce single row given a customerid), no duplication occurs.

Upvotes: 6

quzary
quzary

Reputation: 285

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

Upvotes: 1

Anand Nandakumar
Anand Nandakumar

Reputation: 122

How about doing a count of servers in a sub query and assign that to a query variable and then use that query variable inside the sum function?

Upvotes: 1

Related Questions