DB --> LEC 7 --> Topics --> 31 --> 41

I'll explain each of these SQL functions and concepts in MySQL with detailed explanations, examples, and practical use cases. These are aggregate functions commonly used to perform calculations on data in a MySQL database.

31. SQL MAX() Function

The MAX() function returns the highest (maximum) value in a specified column for a set of rows. It works with numeric, string, and date data types.

Syntax:

SELECT MAX(column_name) FROM table_name;

Details:

Ignores NULL values.
Can be used with GROUP BY to find the maximum value per group.
Works with numbers, dates, and strings (based on collation for strings).

Example: Suppose you have a table employees:

employee_id	salary	hire_date
1	50000	2023-01-15
2	60000	2022-06-10
3	NULL	2021-09-20

SELECT MAX(salary) AS highest_salary FROM employees;

Output:

highest_salary
60000

With GROUP BY:

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

This returns the highest salary for each department.

32. SQL MIN() Function

The MIN() function returns the lowest (minimum) value in a specified column for a set of rows. Like MAX(), it works with numeric, string, and date data types.

Syntax:

SELECT MIN(column_name) FROM table_name;

Details:

Ignores NULL values.
Can be used with GROUP BY to find the minimum value per group.
For strings, it returns the "smallest" value based on the collation (e.g., alphabetically first).

Example: Using the employees table:

SELECT MIN(salary) AS lowest_salary FROM employees;

Output:

lowest_salary
50000

With Dates:

SELECT MIN(hire_date) AS earliest_hire FROM employees;

Output:

earliest_hire
2021-09-20

33. Aliases With MAX() and MIN()

Aliases are used to give a temporary name to the result of MAX() or MIN() for better readability or to reference the result in queries.

Syntax:

SELECT MAX(column_name) AS alias_name FROM table_name;
SELECT MIN(column_name) AS alias_name FROM table_name;

Details:

Aliases are optional but improve clarity in result sets.
Aliases can be used in the SELECT clause but not in the WHERE clause directly (use subqueries for that).

Example:

SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees;

Output:

highest_salary	lowest_salary
60000	50000

With Subquery: To find employees with the highest salary:

SELECT employee_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) AS max_salary FROM employees);

34. MAX() and MIN() With Strings

MAX() and MIN() can be used with string columns to find the lexicographically largest or smallest value based on the column's collation.

Details:

For strings, MAX() returns the "last" value in alphabetical order (e.g., 'Zebra' > 'Apple').
MIN() returns the "first" value (e.g., 'Apple' < 'Zebra').
Case sensitivity depends on the collation (e.g., utf8mb4_general_ci is case-insensitive).

Example: Table products:

product_id	product_name
1	Apple
2	Zebra
3	Banana

SELECT MAX(product_name) AS last_product, MIN(product_name) AS first_product
FROM products;

Output:

last_product	first_product
Zebra	Apple

35. MAX() and MIN() in Nested SELECT

MAX() and MIN() are often used in subqueries (nested SELECT statements) to filter rows based on maximum or minimum values.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name = (SELECT MAX(column_name) FROM table_name);

Details:

Subqueries allow you to compare a column against the maximum or minimum value.
Useful for finding records associated with the max/min value.

Example: Find the employee with the highest salary:

SELECT employee_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Output:

employee_id	salary
2	60000

With Multiple Conditions: Find the earliest hire date per department:

SELECT department_id, hire_date
FROM employees e1
WHERE hire_date = (
    SELECT MIN(hire_date)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

36. SQL COUNT() Function

The COUNT() function returns the number of rows that match a specified condition.

Syntax:

SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;

Details:

COUNT(column_name) counts non-NULL values in the specified column.
COUNT(*) counts all rows, including those with NULL values.
Often used with GROUP BY or WHERE.

Example:

SELECT COUNT(*) AS total_employees FROM employees;

Output:

total_employees
3

SELECT COUNT(salary) AS non_null_salaries FROM employees;

Output:

non_null_salaries
2

37. Specify Column to Count

You can specify a column in COUNT() to count only non-NULL values in that column.

Syntax:

SELECT COUNT(column_name) FROM table_name;

Details:

Useful when you want to count valid (non-NULL) entries.
Different from COUNT(*), which counts all rows.

Example:

SELECT COUNT(salary) AS valid_salaries FROM employees;

Output:

valid_salaries
2

38. COUNT() With WHERE

The COUNT() function can be combined with a WHERE clause to count rows that meet specific conditions.

Syntax:

SELECT COUNT(*) FROM table_name WHERE condition;

Example: Count employees with a salary greater than 55000:

SELECT COUNT(*) AS high_earners
FROM employees
WHERE salary > 55000;

Output:

high_earners
1

39. COUNT() With DISTINCT

COUNT(DISTINCT column_name) counts the number of unique non-NULL values in a column.

Syntax:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Details:

Ignores duplicate values.
Useful for counting unique entries.

Example: Table orders:

order_id	customer_id
1	101
2	101
3	102

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Output:

unique_customers
2

40. SQL SUM() Function

The SUM() function calculates the total sum of numeric values in a column.

Syntax:

SELECT SUM(column_name) FROM table_name;

Details:

Ignores NULL values.
Works only with numeric columns.
Can be used with GROUP BY.

Example:

SELECT SUM(salary) AS total_salary FROM employees;

Output:

total_salary
110000

With GROUP BY:

SELECT department_id, SUM(salary) AS dept_salary
FROM employees
GROUP BY department_id;

41. SQL AVG() Function

The AVG() function calculates the average (mean) of numeric values in a column.

Syntax:

SELECT AVG(column_name) FROM table_name;

Details:

Ignores NULL values.
Works only with numeric columns.
Can be used with GROUP BY.

Example:

SELECT AVG(salary) AS avg_salary FROM employees;

Output:

avg_salary
55000

With ROUND:

SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

Output:

avg_salary
55000.00

Practical Notes for MySQL

Performance: Aggregate functions like MAX(), MIN(), COUNT(), SUM(), and AVG() can be slow on large datasets. Ensure proper indexing on columns used in WHERE or GROUP BY.
NULL Handling: All these functions ignore NULL values except COUNT(*).
Collation: For string comparisons in MAX() and MIN(), the collation of the column affects the sorting (e.g., case-sensitive vs. case-insensitive).
Subqueries: When using MAX() or MIN() in subqueries, ensure the subquery returns a single value for comparison.
Data Types: Ensure the column used with SUM() or AVG() is numeric to avoid errors.

These functions are foundational for data analysis in MySQL, enabling you to summarize and extract insights from your data efficiently. If you need more specific examples or have a particular use case, let me know!

Database SQL

FOX Academy

DB --> LEC 7 --> Topics --> 31 --> 41

31. SQL MAX() Function

32. SQL MIN() Function

33. Aliases With MAX() and MIN()

34. MAX() and MIN() With Strings

35. MAX() and MIN() in Nested SELECT

36. SQL COUNT() Function

37. Specify Column to Count

38. COUNT() With WHERE

39. COUNT() With DISTINCT

40. SQL SUM() Function

41. SQL AVG() Function

Practical Notes for MySQL

You may like these posts

Also Like

DB --> LEC 7 --> Topics --> 31 --> 41

31. SQL MAX() Function

32. SQL MIN() Function

33. Aliases With MAX() and MIN()

34. MAX() and MIN() With Strings

35. MAX() and MIN() in Nested SELECT

36. SQL COUNT() Function

37. Specify Column to Count

38. COUNT() With WHERE

39. COUNT() With DISTINCT

40. SQL SUM() Function

41. SQL AVG() Function

Practical Notes for MySQL

You may like these posts