Using SUM() and AVG() with GROUP BY in SQL Server

by

When working with data, we often need to roll up numbers by categories. For example, calculating sales totals by region, or averaging test scores by class. SQL Server’s SUM() and AVG() functions can work perfectly for this scenario when combined with the GROUP BY clause. This combo can provide quick insights without having to do the math yourself. Let’s walk through how this works with an example.

Example Scenario

Suppose you’re analyzing energy consumption data for a set of office buildings. You have a table called BuildingEnergyUsage that looks like this:

-- Create table
CREATE TABLE BuildingEnergyUsage (
    BuildingID INT,
    Department VARCHAR(50),
    Month INT,
    EnergyConsumedKWh DECIMAL(10,2)
);

-- Insert data
INSERT INTO BuildingEnergyUsage (BuildingID, Department, Month, EnergyConsumedKWh)
VALUES
    (1, 'IT', 1, 1250.50),
    (1, 'IT', 2, 1320.00),
    (1, 'HR', 1, 890.25),
    (2, 'Finance', 1, 1575.75),
    (2, 'Finance', 2, 1620.00),
    (2, 'Operations', 1, 2100.40);

-- Select data
SELECT * FROM BuildingEnergyUsage;

Output:

BuildingID  Department  Month  EnergyConsumedKWh
----------  ----------  -----  -----------------
1           IT          1      1250.5           
1           IT          2      1320             
1           HR          1      890.25           
2           Finance     1      1575.75          
2           Finance     2      1620             
2           Operations  1      2100.4

This table records how much energy (in kilowatt-hours) each department consumed each month in a building. While this provides us with all the required data, if we wanted to know how much energy each department used in total, we’d need to manually add up the rows for that department. The same concept would apply if we wanted the average amounts for each department. Fortunately, we can use the SUM() and/or AVG() functions to do that work for us.

Using SUM() with GROUP BY

If you want to know the total energy consumed by each department across all months, SUM() with GROUP BY is the way to go:

SELECT Department, 
       SUM(EnergyConsumedKWh) AS TotalEnergy
FROM BuildingEnergyUsage
GROUP BY Department;

Result:

Department  TotalEnergy
----------  -----------
Finance     3195.75    
HR          890.25     
IT          2570.5     
Operations  2100.4

This query groups all rows by Department and then calculates the sum of EnergyConsumedKWh for each department.

We can immediately see that Finance and IT are the heavy consumers.

Using AVG() with GROUP BY

If you want the average monthly energy usage per department, you just swap out SUM() for AVG():

SELECT Department, 
       AVG(EnergyConsumedKWh) AS AvgMonthlyEnergy
FROM BuildingEnergyUsage
GROUP BY Department;

Result:

Department  AvgMonthlyEnergy
----------  ----------------
Finance     1597.875        
HR          890.25          
IT          1285.25         
Operations  2100.4

Combining SUM() and AVG()

You can also include both in the same query to get a more complete picture:

SELECT Department,
       SUM(EnergyConsumedKWh) AS TotalEnergy,
       AVG(EnergyConsumedKWh) AS AvgMonthlyEnergy
FROM BuildingEnergyUsage
GROUP BY Department;

Result:

Department  TotalEnergy  AvgMonthlyEnergy
----------  -----------  ----------------
Finance     3195.75      1597.875        
HR          890.25       890.25          
IT          2570.5       1285.25         
Operations  2100.4       2100.4

This provides both the total and the monthly average for each department side by side.

The Benefit of Grouping

By grouping results, you can quickly switch from raw data (lots of rows with individual values) to meaningful summaries that highlight patterns. Instead of scrolling through 12 months of numbers for IT, you can instantly see their total annual consumption and average monthly usage.

The combination of GROUP BY with aggregate functions like SUM() and AVG() can be a very useful tool in SQL Server for reporting, resource planning, and decision-making.

Building on Our Example with ROLLUP()

So far, we’ve used GROUP BY to get totals or averages at one level of detail (per department). But sometimes you also want subtotals or even a grand total in the same result set. That’s where the ROLLUP() modifier can help.

For example, suppose you want to see the total energy consumed by each department and the total across all departments. Instead of writing two queries, you can use ROLLUP():

SELECT Department, 
       SUM(EnergyConsumedKWh) AS TotalEnergy
FROM BuildingEnergyUsage
GROUP BY ROLLUP(Department);

Result:

Department  TotalEnergy
----------  -----------
Finance     3195.75    
HR          890.25     
IT          2570.5     
Operations  2100.4     
null        8756.9

The result includes the same per-department totals as before, plus one extra row with NULL in the Department column. That NULL row represents the grand total.

If you want to make that NULL row more readable, you can replace it with a label using ISNULL() or COALESCE():

SELECT COALESCE(Department, 'All Departments') AS Department,
       SUM(EnergyConsumedKWh) AS TotalEnergy
FROM BuildingEnergyUsage
GROUP BY ROLLUP(Department);

Output:

Department       TotalEnergy
---------------  -----------
Finance          3195.75    
HR               890.25     
IT               2570.5     
Operations       2100.4     
All Departments  8756.9

This outputs a nice summary table with both departmental totals and an overall figure. This enabled us to get the extra information without having to construct any extra query work.