SQL GROUP BY, HAVING, DISTINCT, COUNT, SUM, AVG Fundamentals

SQL GROUP BY fundamentals transform raw data into aggregated insights. Every analytics query involves grouping data by dimensions and calculating metrics. Understanding GROUP BY, HAVING, DISTINCT, COUNT, SUM, AVG—and their interactions—is essential for real-world analytics work.

Understanding SQL GROUP BY Fundamentals

SQL GROUP BY fundamentals start with understanding what grouping does: it partitions rows into groups based on column values. A sales table with rows for each transaction becomes a summary grouped by product, customer, or date. GROUP BY is how raw data becomes dashboards and reports.

DISTINCT vs GROUP BY: When to Use Each

DISTINCT removes duplicate rows. GROUP BY organizes rows into groups and applies aggregates. They seem similar but serve different purposes. SQL GROUP BY fundamentals teach that DISTINCT is simpler: SELECT DISTINCT product returns each product once. SELECT product FROM … GROUP BY product does the same, but allows aggregates.

COUNT: The Most Common Aggregate

SQL GROUP BY fundamentals rely heavily on COUNT. COUNT(*) counts all rows, including those with NULLs. COUNT(column) counts non-NULL values in that column. For a sales table with 1000 rows and 100 rows with NULL amount, COUNT(*) = 1000, but COUNT(amount) = 900.

SUM and AVG: Calculating Totals and Averages

SUM adds up all values in a column for each group. SUM skips NULLs. AVG calculates the average, and also skips NULLs. SUM(amount) / COUNT(*) ≠ SUM(amount) / COUNT(amount) if NULLs exist. The first divides by total rows, the second by non-NULL rows. AVG automatically uses the non-NULL count.

HAVING: Filtering Groups After Aggregation

WHERE filters rows before grouping. HAVING filters groups after aggregation. This is the core of SQL GROUP BY fundamentals: two different filtering stages. Example: “Find products sold more than 100 units.” WHERE quantity > 100 would filter individual transactions (wrong). HAVING SUM(quantity) > 100 filters groups after summing (correct).

GROUP BY with Multiple Columns: Multi-Dimensional Grouping

GROUP BY can use multiple columns to create multi-dimensional summaries. GROUP BY product, region, year creates groups for each product-region-year combination. This is essential for SQL GROUP BY fundamentals in real analytics.

Common SQL GROUP BY Fundamentals Mistakes

Mistake #1: Including non-grouped columns in SELECT. SELECT product, category, SUM(amount) FROM sales GROUP BY product. If multiple categories exist per product, the database doesn’t know which category to return.

5 Complete Interview Questions with Solutions

Question 1: COUNT(*) vs COUNT(column) with NULLs

A table has 1000 sales rows. 100 rows have NULL category. SELECT category, COUNT(*), COUNT(category) FROM sales GROUP BY category. What’s different and why?

SELECT category, COUNT(*) AS total_rows, 
       COUNT(category) AS non_null_categories
FROM sales
GROUP BY category;

Explanation: COUNT(*) counts all rows in each group, including NULLs. COUNT(category) counts only non-NULL category values. The NULL category group shows COUNT(*) = 100 but COUNT(category) = 0. This reveals data quality issues.

Amazon – Senior Data AnalystGoogle – Analytics EngineerMeta – Senior Analytics Engineer

Question 2: AVG with NULLs – Misleading Results

10 order rows: amounts [100, 200, NULL, 150, NULL, 300, NULL, NULL, 250, 400]. SELECT AVG(amount), SUM(amount), COUNT(*), COUNT(amount). Explain each result.

SELECT AVG(amount) AS avg_amount,
       SUM(amount) AS total_amount,
       COUNT(*) AS total_rows,
       COUNT(amount) AS non_null_amounts
FROM orders;

Answer: AVG(amount) = 233.33 (1400 / 6), SUM = 1400, COUNT(*) = 10, COUNT(amount) = 6. AVG silently uses only non-NULL denominator. If you divide SUM by COUNT(*), you get 140 (wrong).

Google – Senior Data AnalystMicrosoft – BI EngineerFlipkart – Analytics Engineer

Question 3: WHERE vs HAVING with GROUP BY

Find customers who spent more than $5000. Orders table: customer_id, amount. Write using WHERE vs HAVING. Why does WHERE version fail?

-- Correct: HAVING for group filtering
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 5000;

Explanation: WHERE executes before GROUP BY (no aggregates exist yet). HAVING executes after GROUP BY (aggregates exist). WHERE SUM(amount) > 5000 causes syntax error.

Airbnb – Senior Data AnalystLinkedIn – BI EngineerUber – Analytics Engineer

Question 4: DISTINCT in GROUP BY Context

Find the number of distinct customers per product. Products table has many rows per customer (different dates). Write a query. What’s the difference between COUNT(DISTINCT customer_id) and COUNT(customer_id)?

SELECT product_id, 
       COUNT(DISTINCT customer_id) AS unique_customers,
       COUNT(customer_id) AS total_purchases
FROM purchases
GROUP BY product_id;

Answer: COUNT(DISTINCT customer_id) counts unique customers (removes duplicates). COUNT(customer_id) counts all non-NULL purchase records. If customer 5 bought product X twice, DISTINCT shows 1, non-DISTINCT shows 2.

Google – Senior Analytics EngineerAmazon – BI Engineer

Question 5: GROUP BY Multiple Columns

Sales table: product, region, year, amount. Find revenue (SUM) and order count per product-region-year. Show only years with revenue > $100K.

SELECT product, region, year,
       SUM(amount) AS revenue,
       COUNT(*) AS order_count
FROM sales
GROUP BY product, region, year
HAVING SUM(amount) > 100000
ORDER BY revenue DESC;

Explanation: GROUP BY product, region, year creates groups for each combination. HAVING filters to groups with revenue > 100K. ORDER BY ensures consistent result order.

Meta – Senior Data AnalystMicrosoft – Analytics Engineer

15 Practice Questions (Test Yourself)

Test your knowledge with these 15 questions from actual company interviews.

Question 6: When you GROUP BY category and see very low averages, how would you use COUNT(*), COUNT(product_id), and COUNT(DISTINCT product_id) to detect data quality issues?

Amazon – Senior Data AnalystGoogle – Analytics Engineer

Question 7: GROUP BY on a billion-row table is slow when filtering in HAVING. How does pre-filtering with WHERE before GROUP BY improve performance and when should you NOT do this?

Meta – BI EngineerFlipkart – Senior Analytics Engineer

Question 8: You notice a NULL group in GROUP BY region with 10,000 rows. Explain why NULL forms its own group and how would you handle this in production reporting?

Airbnb – Analytics EngineerUber – Senior Data AnalystLinkedIn – BI Engineer

Question 9: Your report shows different top-10 products daily even though data hasn’t changed. What causes non-deterministic results and how do you fix ORDER BY to ensure consistency?

Google – Senior Analytics EngineerOYO – Analytics Engineer

Question 10: Calculate revenue by product separately for returning customers (> 2 purchases) and new customers. How would you use CASE inside aggregate functions?

Stripe – Senior Data AnalystMeta – BI EngineerUber – Analytics Engineer

Question 11: GROUP BY country, category, segment with 200 countries, 50 categories, 10 segments could create 100K groups but you have 50K. What does this data distribution tell you?

Amazon – Senior Analytics EngineerGoogle – BI Engineer

Question 12: Count unique customers last month using COUNT(DISTINCT customer_id) vs GROUP BY customer_id. Compare performance and accuracy – can results ever differ?

Microsoft – BI EngineerAirbnb – Senior Data Analyst

Question 13: Find unique customer-product combinations and their purchase counts. Why does COUNT(DISTINCT customer_id, product_id) fail and how do you fix it?

Flipkart – Senior Analytics EngineerLinkedIn – Data AnalystUber – Senior BI Engineer

Question 14: GROUP BY month from timestamp columns. How would you handle leap years, month-end edge cases, and timezones in a robust query?

Google – Senior Analytics EngineerAmazon – Analytics Engineer

Question 15: Query SELECT product, category, SUM(amount) GROUP BY product fails in standard SQL but works in MySQL. Why and how do you fix it properly?

Meta – BI EngineerMicrosoft – Senior Data Analyst

Question 16: Show revenue separately for shipped, pending, and cancelled orders per product. Use SUM(CASE…). When is this better than multiple separate queries?

Airbnb – Senior Analytics EngineerLinkedIn – Analytics EngineerOYO – Senior BI Engineer

Question 17: What does COUNT(DISTINCT column) return when all values are NULL or table is empty? Test all aggregates under edge cases – which ones return zero vs NULL?

Google – Analytics EngineerAmazon – Senior BI Engineer

Question 18: Dashboard ranks customers by spending. Same data Tuesday shows customer A #1, Wednesday shows B #1 (same total). How do you ensure consistent rankings?

Flipkart – Data AnalystMeta – Senior Data AnalystStripe – Analytics Engineer

Question 19: Calculate each product’s percentage of total revenue using three approaches: JOIN, window functions, subqueries. Which is clearest and most efficient?

Microsoft – Analytics EngineerAirbnb – Senior BI Engineer

Question 20: GROUP BY on 5-billion row table takes 10 minutes with 100K groups. How would you diagnose performance bottlenecks and optimize with indexes or materialized aggregates?

Google – Senior Analytics EngineerAmazon – Analytics EngineerLinkedIn – Senior BI Engineer

Ready to Master SQL GROUP BY & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. To truly ace your analytics interviews and master SQL GROUP BY alongside advanced concepts, get our comprehensive Data Analytics: Ace All The Interview Concepts course.