SQL GROUP BY and aggregate functions

What does the GROUP BY clause do?

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like totals or averages.

True or False: GROUP BY can be used without aggregate functions.

False. GROUP BY is typically used with aggregate functions to summarize data.

GROUP BY is often used with what type of functions?

Aggregate functions such as COUNT, SUM, AVG, MAX, and MIN.

List two common uses of GROUP BY.

- Grouping sales data by month - Counting occurrences of a category

Fill in the blank: GROUP BY is helpful for ______ data.

summarizing

How does GROUP BY affect the result set?

It reduces the number of rows by aggregating data based on specified columns.

Give an example SQL query using GROUP BY.

SELECT state, SUM(sales) FROM orders GROUP BY state;

What happens if you omit columns in GROUP BY?

You will receive an error because all non-aggregated columns must be included.

Compare GROUP BY and ORDER BY.

GROUP BY organizes data into groups; ORDER BY sorts the result set.

When should you consider using GROUP BY?

When you need to summarize data for analysis, such as calculating averages or totals.

True or False: GROUP BY can group by multiple columns.

True. You can specify multiple columns to group by.

What does the following query return? SELECT city, COUNT(*) FROM customers GROUP BY city;

It returns the number of customers in each city.

What does the COUNT function do?

The COUNT function returns the number of rows that match a specified condition in a query.

SUM vs. AVG: What's the difference?

SUM adds all values in a column. AVG calculates the mean of those values.

What is the purpose of the MAX function?

MAX returns the highest value from a specified column in a dataset.

Example: Use SUM to calculate total sales.

Query: SELECT SUM(sale_amount) FROM sales; Result: Total sales amount.

True or False: MIN can return non-numeric values.

True. MIN can find the lowest value, including strings, in a dataset.

How does GROUP_CONCAT work?

GROUP_CONCAT concatenates values from multiple rows into a single string, separated by a specified delimiter.

Which function would you use to find average salary?

Use the AVG function to calculate the average salary from a salary column.

What is the effect of using DISTINCT with COUNT?

COUNT(DISTINCT column_name) returns the number of unique, non-null values in that column.

What does the VARIANCE function measure?

VARIANCE calculates how much values in a dataset differ from the mean.

Fill in the blank: The ______ function counts non-null distinct values.

Answer: COUNT(DISTINCT column_name)

What is the difference between SUM and COUNT?

SUM totals the numeric values, while COUNT counts the number of rows or non-null entries.

Example: Find the highest temperature recorded.

Query: SELECT MAX(temperature) FROM weather; Result: Highest temperature value.

GROUP BY with multiple columns →

Allows aggregation across multiple fields, e.g., SELECT department, COUNT(*) FROM employees GROUP BY department, job_title;

True or False: HAVING applies to individual rows.

False - HAVING filters groups after aggregation, unlike WHERE which filters individual rows.

What is the purpose of the HAVING clause?

It filters groups based on aggregate conditions, e.g., SELECT city, SUM(sales) FROM stores GROUP BY city HAVING SUM(sales) > 10000;

Fill in the blank: HAVING is used in conjunction with ________.

GROUP BY to filter aggregated results.

Comparison: GROUP BY vs. ORDER BY

GROUP BY organizes data into groups for aggregation, whereas ORDER BY sorts the final result set.

What happens when GROUP BY is not used?

Aggregations without GROUP BY will result in a single summary row for the dataset.

Example: Multiple aggregations in one query

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department;

Cause → Effect: Using HAVING after GROUP BY

Filters out groups that do not meet aggregate conditions, refining results to only relevant data.

What does COUNT(DISTINCT column) do?

Counts the unique values in a column, e.g., SELECT COUNT(DISTINCT city) FROM customers;

True or False: You can use aggregate functions in the WHERE clause.

False - Aggregate functions cannot be used in WHERE; use HAVING instead.

Define the role of the GROUP BY clause.

It groups rows sharing a property so aggregate functions can be applied to each group.

What is a common issue with GROUP BY?

Omitting non-aggregated columns in the SELECT statement can cause errors, e.g., all selected fields must be aggregated or grouped.