MySQL Function Guide

GROUP_CONCAT Function

GROUP_CONCAT is a powerful MySQL aggregate function used to concatenate strings from a group into a single string.

Basic Syntax:

GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

Parameters:

DISTINCT: Optional, used to remove duplicate values from the result
expr: The expression or column name to be concatenated
ORDER BY: Optional, used to sort the result
SEPARATOR: Optional, specifies the separator string, default is comma

Example:

Suppose we have an employees table with fields: id, name, department

SELECT department, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

This query will return each department and all its employee names (sorted alphabetically and separated by commas).

Note: The result length of GROUP_CONCAT is limited by the system variable group_concat_max_len, which typically defaults to 1024. You can increase this limit by setting this variable.

1. COUNT()

COUNT() function is used to count the number of rows or non-NULL values.

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

2. SUM()

SUM() function is used to calculate the sum of a set of values.

SELECT SUM(salary) AS total_salary FROM employees;

3. AVG()

AVG() function is used to calculate the average of a set of values.

SELECT AVG(price) AS average_price FROM products;

4. CONCAT()

CONCAT() function is used to concatenate two or more strings.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;