Study guide

Technical reference and lesson notes

Purpose of This Lesson

This lesson reviews SQL operations that commonly appear in data-engineering work and are relevant to the AWS Certified Data Engineer Associate learning path: aggregations, conditional aggregation, grouping, sorting, and pivoting. It is a focused review rather than a complete SQL course.

Key Concepts

Aggregation Functions

Aggregation functions reduce multiple rows to one or more summary values:

COUNT(*) counts rows, including rows regardless of the values in individual columns.
SUM(column) adds numeric values.
AVG(column) calculates the average of numeric values.
MAX(column) returns the greatest value.
MIN(column) returns the smallest value.
AS assigns an alias to an expression or result column.

Examples:

SELECT COUNT(*) AS total_rows
FROM employees;

SELECT SUM(salary) AS total_salary,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees;

Filtering Aggregated Results

Use WHERE when the entire aggregation should operate only on rows matching one filter:

SELECT COUNT(*) AS high_salary_count
FROM employees
WHERE salary > 70000;

For several independent counts in one result row, use conditional aggregation with CASE. Each matching row contributes 1; nonmatching rows produce NULL when no ELSE expression is specified, and COUNT counts the non-null results.

SELECT
    COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary BETWEEN 50000 AND 70000 THEN 1 END) AS medium_salary_count,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_salary_count
FROM employees;

This approach produces multiple conditional metrics at once instead of filtering the entire input to one condition.

Grouping

GROUP BY divides rows into groups based on one or more column values, then calculates aggregates separately for each group:

SELECT department_id,
       COUNT(*) AS number_of_employees
FROM employees
WHERE join_date > '2020-01-01'
GROUP BY department_id;

The WHERE clause is evaluated before grouping, so only employees who joined after the specified date participate in the department counts. The result contains one row per unique department_id represented in the filtered data.

Grouping by multiple columns creates a group for each distinct combination. For example:

SELECT sale_year,
       product_id,
       SUM(amount) AS total_sales
FROM sales
GROUP BY sale_year, product_id;

This returns a separate result row for each sale_year and product_id combination, not merely one row per year or one row per product.

Sorting

ORDER BY sorts the final result. Multiple sort expressions are evaluated in sequence:

ORDER BY sale_year, total_sales DESC;

This sorts by year first and then sorts products within each year by total_sales from highest to lowest. The example combines nested grouping and sorting to show annual product performance.

Pivoting

Pivoting transforms row-level values into columns. For sales data, rows containing a salesperson, month, and sales amount can become one row per salesperson with separate columns such as January sales and February sales.

Pivot syntax varies by database. Some systems provide a PIVOT operation. A more portable alternative is conditional aggregation:

SELECT
    salesperson,
    SUM(CASE WHEN month = 'January' THEN sales ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN month = 'February' THEN sales ELSE 0 END) AS feb_sales
FROM sales
GROUP BY salesperson;

The CASE expression contributes the sales amount for the selected month and contributes zero for other months. Repeating the pattern for each desired month creates the pivoted columns.

Technical / Operational Context

A common analytical workflow is:

Restrict input rows with WHERE when a row-level filter is required.
Group the remaining rows with GROUP BY when summaries are needed per category or combination.
Apply aggregate functions such as COUNT, SUM, or AVG.
Sort the resulting summary rows with ORDER BY.
Reshape the output with a database-specific PIVOT operation or conditional aggregation when consumers need categories represented as columns.

The placement of logic matters. A WHERE filter removes rows before aggregation. A CASE inside an aggregate selectively contributes values to one metric while allowing other metrics to be calculated in the same query.

Pivoting is primarily a presentation and reporting transformation. The exact syntax is database-specific, so understanding the shape change—rows becoming columns—is more important than memorizing one vendor’s command.

Exam- or Assessment-Relevant Takeaways

Recognize COUNT(*), SUM, AVG, MAX, and MIN as basic aggregation tools.
Use WHERE for a filter that applies to the full input set before aggregation.
Use conditional aggregation with CASE when several filtered aggregates must be returned together.
Expect one output row per unique grouping key, or per unique combination when grouping by multiple columns.
Understand that ORDER BY sale_year, total_sales DESC sorts by year and then orders rows within each year by descending sales.
Know that pivoting changes row-oriented data into column-oriented output.
Do not assume PIVOT syntax is portable across databases; conditional aggregation can provide an alternative.

Tool / Feature Decision Guide

Requirement	Recommended approach	Reason
Count every row in a table	`COUNT(*)`	Produces the total row count.
Calculate one summary over a filtered population	Aggregate plus `WHERE`	The filter limits the rows before aggregation.
Calculate multiple independent counts or sums in one query	Conditional aggregation with `CASE`	Each expression applies its own condition.
Produce summaries for each department or category	`GROUP BY` one column	Creates one result group per distinct value.
Produce summaries for each year-and-product combination	`GROUP BY` multiple columns	Creates one group per distinct combination.
Order summary results	`ORDER BY`	Sorts the query output after the result expressions are formed.
Reshape monthly rows into month columns when supported	Database-specific `PIVOT`	Directly expresses the row-to-column transformation, but syntax varies.
Reshape data without a dedicated pivot feature	Conditional aggregation	More portable, though potentially less direct or efficient.

Common Traps / Misconceptions

WHERE does not create several independent aggregates in one result row; it filters the entire input for that query.
GROUP BY department_id does not return one total for the whole table. It returns a separate aggregate for each department represented after filtering.
Grouping by sale_year, product_id means combinations matter. A product sold in two years can produce two result rows.
ORDER BY controls presentation order; it does not change the grouping logic.
Pivoting is not simply sorting or grouping. It changes the orientation of the result by turning row categories into columns.
PIVOT syntax is not universal across database systems.
Conditional COUNT(CASE WHEN ... THEN 1 END) relies on nonmatching rows producing NULL, which COUNT(expression) does not count.
Conditional SUM should use ELSE 0 when nonmatching rows should contribute no amount to the total.

Real-World Engineer / Analyst Notes

Use clear aliases such as total_salary, average_salary, and number_of_employees; descriptive output names make downstream analysis easier.
Before trusting a grouped result, confirm whether filters should apply before grouping. A date restriction such as join_date > '2020-01-01' changes the population being counted.
When multiple dimensions are grouped together, verify the intended grain. A year-and-product result has a different grain from a year-only result.
Conditional aggregation is useful for dashboards that need several categories as separate measures in one row per entity.
Choose a native PIVOT feature when the database supports it and its syntax fits the workload; otherwise, conditional aggregation can express the same basic transformation.
Treat pivoting as a consumer-oriented shape change. For reusable analytical data, retain a clear row-oriented source model when possible and pivot at the reporting boundary.

Quick Reference Summary

-- Basic aggregation
SELECT COUNT(*) AS total_rows,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees;

-- Filter before aggregation
SELECT COUNT(*) AS high_salary_count
FROM employees
WHERE salary > 70000;

-- Several conditional aggregates
SELECT COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_salary_count,
       COUNT(CASE WHEN salary BETWEEN 50000 AND 70000 THEN 1 END) AS medium_salary_count,
       COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_salary_count
FROM employees;

-- Group, aggregate, and sort
SELECT sale_year, product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY sale_year, product_id
ORDER BY sale_year, total_sales DESC;

-- Portable pivot pattern
SELECT salesperson,
       SUM(CASE WHEN month = 'January' THEN sales ELSE 0 END) AS jan_sales,
       SUM(CASE WHEN month = 'February' THEN sales ELSE 0 END) AS feb_sales
FROM sales
GROUP BY salesperson;

Flashcards

Q: A report needs the total number of rows in employees, regardless of column values. Which expression should you use?

A: Use COUNT(*). It counts rows in the input table.

Q: When should you use WHERE instead of conditional aggregation?

A: Use WHERE when one filter should restrict the entire population being aggregated. Use conditional aggregation when different aggregates need different conditions in the same result.

Q: A query must return high-, medium-, and low-salary employee counts in one row. Which technique fits best?

A: Use separate COUNT(CASE WHEN ... THEN 1 END) expressions. Each expression counts only rows satisfying its own salary condition.

Q: Why does COUNT(CASE WHEN salary > 70000 THEN 1 END) count only high-salary employees?

A: Matching rows return 1, while nonmatching rows return NULL because there is no ELSE. COUNT(expression) counts the non-null results.

Q: What is the difference between WHERE and GROUP BY in a department-count query?

A: WHERE removes rows before aggregation; GROUP BY department_id then creates a separate aggregate group for each remaining department.

Q: What output grain results from GROUP BY sale_year, product_id?

A: One row per distinct combination of year and product. The same product can appear in multiple rows if it was sold in multiple years.

Q: How would you sort products within each year from highest to lowest sales?

A: Use ORDER BY sale_year, total_sales DESC. Year is the primary sort key, and descending total sales orders rows within each year.

Q: A query must calculate one total salary for employees who earn more than 70,000. Would you use WHERE or CASE?

A: Use WHERE salary > 70000 with SUM(salary). There is only one population filter, so conditional aggregation is unnecessary.

Q: What does pivoting do to row-level data?

A: It transforms values represented across rows into columns. For example, monthly sales rows can become January and February sales columns.

Q: When might conditional aggregation be preferable to PIVOT?

A: Use conditional aggregation when the database lacks a dedicated pivot operation or when a more portable SQL pattern is desired. The exact PIVOT syntax varies by database.

Q: In a conditional SUM, why is ELSE 0 commonly used?

A: It ensures nonmatching rows contribute zero to that category’s total rather than another value. For example, January sales can sum January amounts while all other months contribute zero.

Q: What is the difference between grouping by a year and grouping by year plus product?

A: Year-only grouping produces one summary per year. Adding product creates a separate summary for each product within each year.

Practice Questions

Question 1

An analyst needs one result row containing separate counts for employees earning above 70,000, between 50,000 and 70,000, and below 50,000. Which approach is most appropriate?

A. Use three separate queries with different WHERE clauses

B. Use GROUP BY salary and COUNT(*)

C. Use multiple conditional aggregate expressions with CASE

D. Use ORDER BY salary DESC

Correct answer: C. Conditional aggregation allows each count to apply a different condition while returning all metrics together.

Question 2

A data engineer runs a query that filters employees to those who joined after January 1, 2020, then groups by department_id. What does each output row represent?

A. Every employee, sorted by department

B. Every department, including departments with no employees

C. The count of qualifying employees for one department

D. The count of all employees in the company

Correct answer: C. WHERE first limits the rows, and GROUP BY department_id produces one aggregate group for each department represented in that filtered data.

Question 3

A sales table contains one row per sale with a year, product, and amount. The required output must list products within each year from the largest total sales amount to the smallest. Which clause is decisive?

A. ORDER BY sale_year, total_sales DESC

B. GROUP BY total_sales, sale_year

C. WHERE total_sales DESC

D. COUNT(product_id)

Correct answer: A. Grouping creates the year-product summaries, and this ORDER BY sorts years first and sales totals descending within each year.

Question 4

A database does not provide a convenient PIVOT command. The source has salesperson, month, and sales columns, and the report needs January and February as separate columns. What should the engineer use?

A. COUNT(*) without grouping

B. Conditional SUM(CASE WHEN ...) expressions grouped by salesperson

C. MAX(month) grouped by sales amount

D. ORDER BY month only

Correct answer: B. Conditional aggregation can turn month values into separate summed columns and is an alternative to database-specific pivot syntax.

WordPress Metadata

Suggested Slug:
sql-aggregations-grouping-sorting-pivoting

Meta Description:
Review SQL aggregations, conditional filtering, grouping, sorting, and pivoting patterns for AWS Certified Data Engineer Associate preparation.

Tags:
SQL, data engineering, AWS Certified Data Engineer Associate, aggregations, GROUP BY, ORDER BY, conditional aggregation, CASE statements, pivoting, data analytics

SQL Aggregations, Grouping, Sorting, and Pivoting | AWS Data Engineer Associate