💻 Примеры: Summary session 5

Пример 1. Базовые статистики по себестоимости

SELECT AVG(standard_cost) AS avg_cost,
       MIN(standard_cost) AS min_cost,
       MAX(standard_cost) AS max_cost,
       SUM(standard_cost) AS total_cost
FROM products;

Пример 2. Фильтрация групп

SELECT category, COUNT(id) AS product_count
FROM products
GROUP BY category
HAVING COUNT(id) >= 3
ORDER BY product_count DESC;

Пример 3. Группировка по нескольким столбцам

SELECT supplier_ids, category, AVG(standard_cost) AS avg_cost
FROM products
GROUP BY supplier_ids, category
ORDER BY avg_cost DESC;

Пример 4. Условные категории

SELECT CASE
         WHEN reorder_level < 10 THEN 'low'
         WHEN reorder_level BETWEEN 10 AND 20 THEN 'medium'
         ELSE 'high'
       END AS stock_level,
       AVG(list_price) AS avg_price,
       MIN(list_price) AS min_price,
       MAX(list_price) AS max_price
FROM products
GROUP BY stock_level;

Пример 5. WHERE + GROUP BY + HAVING

SELECT category, SUM(list_price - standard_cost) AS total_profit
FROM products
WHERE target_level > 40
GROUP BY category
HAVING total_profit > 0
ORDER BY total_profit DESC;