Combining Aggregation Techniques

Putting It All Together

You’ve learned the pieces: aggregate functions, GROUP BY, HAVING, NULL handling, conditional aggregation. Real analysis usually requires combining several of these in one query.

Let’s walk through building a complex query step by step.

Building Queries Incrementally

Here’s a business question: Find departments with more than 2 employees hired after 2022, showing their headcount and average salary, sorted by average salary.

Don’t need to write it all at once. Build it piece by piece, testing after each step.

Headcount by Department

Run it. Check the output makes sense before moving to the next step.

Step 2: Only Hires After 2022

Now add the row-level filter.

Oracle doesn’t implicitly cast string literals to dates — use a DATE literal or TO_DATE(). The other three dialects accept the plain string format directly.

Now you’re only counting recent hires. Check that the numbers changed.

Step 3: Departments with More Than 2 Such Hires

Only departments meeting the threshold show up now.

Step 4: Add Salary Stats and Sort

Now you’ve answered the full business question. Each step was testable on its own.

Combining GROUP BY with Conditional Aggregation

You can use CASE WHEN inside grouped queries for detailed breakdowns:

SELECT
  department,
  COUNT(*) AS total,
  SUM(CASE WHEN salary < 80000 THEN 1 ELSE 0 END) AS under_80k,
  SUM(CASE WHEN salary >= 80000 THEN 1 ELSE 0 END) AS over_80k,
  ROUND(AVG(salary), 0) AS avg_salary
FROM techcorp_workforce
GROUP BY department
ORDER BY total DESC;

Each department row now shows the salary distribution within that department.

The Query Building Checklist

When writing complex aggregation queries, follow this order:

1. Start with SELECT and FROM
2. Add WHERE if you need to filter individual rows
3. Add GROUP BY with the columns you want to segment by
4. Add HAVING if you need to filter based on aggregate values
5. Add ORDER BY to sort your results
6. Test after each step

Top 10 Songs 2010

Filter by year with WHERE, group appropriately, then use ORDER BY and LIMIT.

Department Workforce Analysis

Filter with WHERE before grouping, then use HAVING to filter groups.

Key Takeaways

You now have a complete toolkit for summarizing and analyzing data:

• COUNT, SUM, AVG, MIN, MAX for basic aggregation
• GROUP BY to segment your data
• HAVING to filter aggregated results
• Understanding of how NULLs behave in aggregates
• CASE WHEN for conditional aggregation and pivots
• The ability to combine all these techniques

What’s Next

So far, all your queries have worked with a single table. But real databases have data spread across multiple tables: customers in one, orders in another, products in a third. Module 3 introduces JOINs, which let you combine data from multiple tables. That’s when things get really powerful.