Aggregate Functions & GROUP BY
7 questions found
What are aggregate functions in SQL Server, and what are the most common ones?
Beginner
Aggregate functions calculate a single summary value from a set of rows, such as counting how many rows exist, adding up a column, or finding the average, minimum, or maximum value. The most common ones are COUNT, SUM, AVG, MIN, and MAX, and they are frequently used to build reports and summaries from large tables.
SELECT COUNT(*) AS TotalOrders, SUM(Amount) AS TotalRevenue, AVG(Amount) AS AverageOrder
FROM Orders;
Real-world example
A sales manager runs a query using SUM and AVG to see the total revenue and average order value for the past month, quickly understanding overall sales performance without reviewing every individual order.
Common follow-ups: What is the difference between COUNT(*) and COUNT(column_name)?;Do aggregate functions ignore NULL values by default?
Normalization;Query Optimization & Plans
How does the GROUP BY clause work together with aggregate functions?
Beginner
GROUP BY splits your rows into separate groups based on the values in one or more columns, and then any aggregate function in your query calculates its result separately for each of those groups instead of for the entire table at once, letting you summarize data by category.
SELECT CustomerId, SUM(Amount) AS TotalSpent
FROM Orders
GROUP BY CustomerId;
Real-world example
An online store groups orders by customer id to see exactly how much each individual customer has spent in total, rather than just one grand total across all customers combined.
Common follow-ups: What columns are allowed in the SELECT list when using GROUP BY?;Can you group by more than one column at the same time?
Joins;Window Functions
How do you filter the results of a grouped query based on an aggregated value, such as only showing customers who spent more than a certain amount?
Intermediate
You use the HAVING clause instead of WHERE to filter after grouping and aggregation have already happened, since WHERE filters individual rows before grouping occurs, while HAVING filters the summarized group results themselves based on the aggregate calculation.
SELECT CustomerId, SUM(Amount) AS TotalSpent
FROM Orders
GROUP BY CustomerId
HAVING SUM(Amount) > 1000;
Real-world example
A marketing team identifies their top spending customers by filtering grouped order totals with HAVING, targeting only those who have spent over a thousand dollars for a loyalty campaign.
Common follow-ups: Why can't you use an aggregate function directly inside a WHERE clause?;Can you combine both WHERE and HAVING in the same query?
Query Optimization & Plans;Subqueries
How do you correctly combine WHERE and HAVING in the same query to filter both individual rows and grouped results?
Intermediate
You place WHERE before GROUP BY to filter out individual rows early, based on conditions that do not depend on aggregation, and then place HAVING after GROUP BY to filter the resulting groups based on the aggregate calculations, giving you precise control over both stages of filtering.
SELECT CustomerId, SUM(Amount) AS TotalSpent
FROM Orders
WHERE OrderDate >= '2026-01-01'
GROUP BY CustomerId
HAVING SUM(Amount) > 500;
Real-world example
A report only considers orders placed this year using WHERE, then further narrows the results to customers who spent over five hundred dollars during that period using HAVING.
Common follow-ups: Does filtering early with WHERE improve query performance compared to filtering everything with HAVING?;What happens if you try to use an aggregate function inside WHERE by mistake?
Query Optimization & Plans;Indexes
How do the ROLLUP and CUBE extensions to GROUP BY help you generate subtotal and grand total rows in a single query?
Advanced
ROLLUP generates subtotal rows for each level of a hierarchy along with a final grand total, while CUBE generates subtotals for every possible combination of the grouped columns, both saving you from writing several separate queries and manually combining their results to build a complete summary report.
SELECT Region, ProductCategory, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, ProductCategory);
Real-world example
A regional sales report uses ROLLUP to show sales totals by product category within each region, subtotals for each region overall, and a single grand total row for the entire company, all in one query.
Common follow-ups: How do you distinguish a subtotal row from a regular detail row in the result set?;What is the performance impact of using CUBE on a very large table?
Window Functions;Query Optimization & Plans
How would you calculate a running total or a percentage of total using aggregate functions combined with window functions?
Advanced
You use a window function like SUM combined with an OVER clause to calculate a running total or a total across the whole result set without collapsing rows through GROUP BY, letting you show both individual row detail and an aggregated comparison value side by side in the same result.
SELECT OrderId, Amount,
SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal,
Amount * 100.0 / SUM(Amount) OVER () AS PercentOfTotal
FROM Orders;
Real-world example
A finance dashboard shows each order alongside a running total of revenue for the month and what percentage that single order represents of the overall total, all without losing any individual order detail.
Common follow-ups: What is the difference between using GROUP BY and using a window function for this kind of calculation?;How does the ORDER BY inside OVER affect a running total calculation?
Window Functions;Query Optimization & Plans
What is the difference between COUNT(*), COUNT(1), and COUNT(column_name) in SQL Server?
Intermediate
COUNT(*) and COUNT(1) both count every row regardless of NULL values and generally perform the same, while COUNT(column_name) only counts rows where that specific column has a non NULL value, which is useful when you specifically want to know how many rows actually have data in a particular field.
SELECT COUNT(*) AS TotalCustomers, COUNT(PhoneNumber) AS CustomersWithPhone
FROM Customers;
Real-world example
A customer database reports both the total number of customers and, separately, how many of them actually have a phone number on file, using COUNT(*) and COUNT(PhoneNumber) together in one query.
Common follow-ups: Does SQL Server treat COUNT(*) and COUNT(1) differently in terms of performance?;How do you count only distinct values within a column?
Data Types & Schema Design;Constraints (Primary Key
Foreign Key
Check & Unique)