GROUP BY with aggregate functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the GROUP BY clause do in SQL?

It groups rows that have the same values in specified columns into summary rows, like grouping all sales by each store.

beginner

Name one common aggregate function used with GROUP BY.

Examples include COUNT(), SUM(), AVG(), MIN(), and MAX(). They calculate values like totals or averages for each group.

beginner

Why do we use aggregate functions with GROUP BY?

Because GROUP BY creates groups of rows, aggregate functions summarize data within each group, like counting items or finding averages.

intermediate

What happens if you use a column in SELECT that is not in GROUP BY or an aggregate function?

SQL will give an error because it doesn't know how to combine that column's values for each group.

beginner

Write a simple SQL query to find the total sales per store using GROUP BY and SUM().

Example: <br>SELECT store_id, SUM(sales) AS total_sales FROM sales_table GROUP BY store_id;

What does GROUP BY do in an SQL query?

A Groups rows with the same values in specified columns

B Deletes duplicate rows

C Sorts rows alphabetically

D Filters rows based on a condition

GROUP BY groups rows that share the same values in the chosen columns, so aggregate functions can summarize each group.

Which aggregate function counts the number of rows in each group?

A SUM()

B AVG()

C MAX()

D COUNT()

COUNT() returns the number of rows in each group.

What will happen if you select a column not in GROUP BY or an aggregate function?

A SQL will return the first value

B SQL will return NULL

C SQL will give an error

D SQL will ignore the column

SQL requires all selected columns to be either grouped or aggregated; otherwise, it throws an error.

Which SQL clause is used to group rows before applying aggregate functions?

A GROUP BY

B ORDER BY

C WHERE

D HAVING

GROUP BY groups rows so aggregate functions can summarize each group.

How do you find the average price per category in a products table?

A SELECT AVG(price) FROM products;

B SELECT category, AVG(price) FROM products GROUP BY category;

C SELECT category, price FROM products;

D SELECT category, SUM(price) FROM products;

This query groups products by category and calculates the average price for each group.

Explain how GROUP BY works with aggregate functions in SQL.

Think about how you would summarize sales by store.

Describe a situation where using GROUP BY with SUM() would be helpful.

Imagine you want to know total sales for each store.