💻 Примеры

💻 Примеры

Базовые агрегаты

-- Общее количество товаров в заказах
SELECT SUM(quantity) AS total_quantity
FROM order_details;

-- Количество уникальных заказов
SELECT COUNT(DISTINCT order_id) AS unique_orders
FROM order_details;

-- Средняя, минимальная и максимальная цена
SELECT AVG(unit_price) AS avg_price,
       MIN(unit_price) AS min_price,
       MAX(unit_price) AS max_price
FROM order_details;

GROUP_CONCAT

-- Имена сотрудников через запятую
SELECT GROUP_CONCAT(first_name) AS all_names
FROM employees;

-- Список городов без повторов
SELECT GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR ', ') AS cities
FROM employees;

GROUP BY

-- Количество сотрудников в каждом городе
SELECT city, COUNT(id) AS number_employees
FROM employees
GROUP BY city
ORDER BY number_employees DESC;

-- Общее количество товаров в каждом заказе
SELECT order_id, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY order_id
ORDER BY total_quantity DESC;

-- Покупатели-менеджеры по закупкам в каждой компании
SELECT company, COUNT(id) AS number_managers
FROM customers
WHERE job_title = 'Purchasing Manager'
GROUP BY company;

-- Группировка по нескольким столбцам
SELECT company, job_title, COUNT(id) AS number_employees
FROM employees
GROUP BY company, job_title;

Группировка по порядковым номерам

SELECT city, COUNT(id) AS number_employees
FROM employees
GROUP BY 1
ORDER BY 2 DESC;