SQL SELECT – Using COUNT() and SUM() in Calculations

select

I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record we track bandwidth usage for that user (for the month).

I need to do a select that gives me all companies that have used over 1GB of bandwidth per user. I can get this company by company using:

select count(*) as numUsers, 
  sum(BandwidthThisMonth) as totalBandwidth 
from [User] 
where [User].CompanyId = @CompanyId

The problem with that approach is I have to first get the CompanyId of every company, and then one by one pull this data. And I only need to find the cases where the usage is over 1GB/user, which is rare.

Is there a way to do something like this:

select 
  CompanyId, 
  count(*) as numUsers, 
  sum(BandwidthThisMonth) as totalBandwidth 
from Company, [User] 
where totalBandwidth - (numUsers*1000000000))/1000000000 > 0

Where the count() & sum() are against just the User records where User.CompanyId = Company.CompanyId?

Best Answer

GROUP BY and the HAVING clause are what you are looking for. GROUP BY allows you to use aggregate functions without having to only have 1 row of data as you currently do. Here, we're going to compute the count of all the users and to total bandwidth for all the users at that company. I don't quite get your tipping point so the logic there may be off. I guess I'm reading it as "we're sharing 1GB of data across all these users"

The HAVING is similar to our WHERE but operates on the GROUPed BY data. Here I've specified that I only want rows where the total bandwidth is over 1GB.

Unless you need something from the Company table, there's no real reason to add them to the query since all of your data is on the User record.

SELECT
    U.CompanyId
,   COUNT(*) as numUsers
,   SUM(U.BandwidthThisMonth) as totalBandwidth 
FROM 
    Company C
    INNER JOIN
        [User] U
        ON U.CompanyId = C.CompanyId
GROUP BY
    U.CompanyId
HAVING
    -- this assumes storage vendor math and not true GB
    -- As a consumer, I'd expect 1GB of bandwidth to be 1 073 741 824
    SUM(U.BandwidthThisMonth) > 1000000000;
Related Question