AVG

Hook: Interviewers love AVG because it looks simple, but it quietly tests whether you know how SQL treats NULL, types, and grouping.

Question: What does AVG do in SQL?

Answer: AVG is an aggregate function that returns the arithmetic mean, which is the sum of the values divided by how many values there are. It ignores NULL values, so missing data does not count in the calculation. If there are no non-NULL rows, the result is NULL, not zero.

Interview-Ready Answer: In SQL, AVG calculates the arithmetic mean of a numeric column. It skips NULLs automatically, so only real values count in the numerator and denominator. One detail interviewers like is that the return type and precision can depend on the database and input type, so if I need exact decimals, I often cast explicitly.

What AVG really means

Detailed Explanation: Think of AVG as a two-step shortcut: add the numbers, then divide by how many numbers you actually have. In SQL, that is an aggregate operation, meaning it combines many rows into one result. The key idea is that AVG is not “middle value” or “most common value”; it is specifically the arithmetic mean.

How it works under the hood

1. The database reads each row in the chosen group.
2. It ignores rows where the target expression is NULL.
3. It keeps a running total and a running count of non-NULL values.
4. At the end of the group, it divides total by count.
5. If the count is zero, the result is NULL because there is nothing to average.

This is why AVG is often mentally paired with SUM and COUNT. In fact, many interview answers are stronger if you can say that AVG(x) is conceptually like SUM(x) / COUNT(x), except SQL handles the NULL rules for you.

When to use it

• Finding average order value in an e-commerce system.
• Finding average response time in an API dashboard.
• Finding average salary, rating, or session duration.
• Comparing groups, such as average sales per region or per month.

Comparison with related functions

Why this matters: average can be misleading if one value is huge. For example, salaries of 50k, 52k, and 400k give an average of 167k, which does not describe a typical employee well. That is a great interview insight: the function is mathematically correct, but sometimes not the best business metric.

Precision, types, and gotchas

Different databases may return different result types. Some systems promote integer inputs to a decimal or numeric result; others may preserve or widen types differently. The safe habit is to cast when precision matters, for example averaging cents, ratings, or ratios. Also, do not average text dates or booleans directly unless your database explicitly allows a numeric conversion, because that behavior is not portable.

Performance note: AVG is usually O(n) over the rows in the group because the engine must inspect each row once. Memory usage is typically small because the engine only needs a running sum and count, not the full list of values. If you add GROUP BY, the database may use hashing or sorting, and cost then depends on the number of groups and indexes, but the aggregate itself is still linear in the number of rows scanned.

Important edge cases

• AVG(NULL) over all-null input returns NULL.
• AVG ignores NULL values instead of treating them as zero.
• For very large values, floating-point rounding can matter, so exact numeric types are safer when business accuracy is important.
• If you filter rows with WHERE, you change the denominator because only surviving rows are averaged.

Memory Hook: Picture a classroom average: you add only the students who actually showed up, then divide by the number who showed up. Empty seats do not count.

Real-World Example: In a checkout service, a data analyst might track the average order amount per day to spot unusual drops. If the average suddenly falls, it could mean discounts are too aggressive, a payment bug is causing partial orders, or high-value items are not being sold.

What goes wrong: suppose a team accidentally stores failed payments as 0 instead of NULL. The daily AVG(order_amount) drops sharply, dashboards show a false revenue problem, and on-call engineers waste time investigating a non-issue. In logs, you might see lots of “zero-value order” rows; users might not see failures directly, but leadership sees misleading KPIs and makes bad decisions. The fix is to store missing values as NULL when the value is truly unknown, and to filter or clean the data before averaging.

Follow-up & Tricky Questions:

• What happens to NULL values in AVG? They are ignored, so they do not affect either the total or the count. If every value is NULL, the result is NULL.
• How is AVG(x) related to SUM(x) and COUNT(x)? Conceptually it is SUM(x) / COUNT(x), using only non-NULL values. That mental model helps when you need custom logic or filters.
• Can I use AVG with GROUP BY? Yes. Each group gets its own average, which is how you build “average by region,” “average by month,” or “average by user type.”
• Why might my average look rounded or wrong? The database may use a floating-point or limited-precision type, so decimals can be rounded. Casting to a precise numeric type helps when exactness matters.
• What is the difference between AVG and MEDIAN? AVG uses every value and can be pulled by outliers; MEDIAN is the middle value and is more robust when data is skewed.

Tricky / gotcha questions:

• Does AVG(0) return NULL because zero is “empty”? No. Zero is a real numeric value, so it counts as a valid row and the result is zero if all values are zero.
• If I write AVG(CASE WHEN condition THEN amount END), what happens to rows that do not match? The CASE returns NULL for non-matching rows, and AVG ignores them. This is a common and clean filtering pattern.
• Is AVG always exact? Not necessarily. Exactness depends on the underlying data type and database engine, so for money or critical metrics, use the right numeric type and cast intentionally.

Common Mistakes:

• Treating NULL as zero. Correction: AVG ignores NULL; it does not convert it to 0.
• Forgetting that AVG can be skewed by outliers. Correction: use MEDIAN or percentile logic when you need a “typical” value for skewed data.
• Assuming the result type is identical in every database. Correction: check your engine’s return type rules and cast when precision matters.
• Using COALESCE(AVG(x), 0) too casually. Correction: returning 0 for “no data” may hide a data-quality problem; only do this when 0 is a meaningful business default.

Memory Hook: “Average = sum of the people who showed up, divided by the people who showed up.” Missing values do not attend the meeting.

Cheat Sheet:

• AVG computes the arithmetic mean.
• It ignores NULL values.
• AVG with no non-NULL rows returns NULL.
• Use GROUP BY for per-group averages.
• For exact decimals, cast intentionally.
• Outliers can distort the mean; consider MEDIAN for skewed data.

Practice Tasks:

• Write a query that returns the average salary from an employees table and ignores missing salaries.
• Group the same table by department and return the average salary per department.
• Modify the query so that departments with only missing salaries show 0 instead of NULL, then explain whether that is a good idea.