Aggregate ও GROUP BY

Aggregate Functions

-- মোট user
SELECT COUNT(*) FROM users;

-- গড়, যোগফল, মিন, ম্যাক্স
SELECT AVG(price), SUM(total), MIN(price), MAX(price) FROM products;

-- অনন্য গণনা
SELECT COUNT(DISTINCT city) FROM users;

-- একসাথে
SELECT
    COUNT(*) AS total,
    SUM(total) AS revenue,
    AVG(total) AS avg_order
FROM orders;

GROUP BY

-- প্রতি city-তে user
SELECT city, COUNT(*) AS count
FROM users
GROUP BY city;

-- প্রতি category-র stats
SELECT category_id,
    COUNT(*) AS products,
    AVG(price) AS avg_price,
    MAX(price) AS highest
FROM products
GROUP BY category_id;

-- একাধিক column
SELECT city, gender, COUNT(*)
FROM users
GROUP BY city, gender;

GROUP BY + JOIN

-- প্রতি user-এর order + টাকা
SELECT u.name,
    COUNT(o.id) AS orders,
    SUM(o.total) AS spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY spent DESC;

HAVING — group filter

-- ৩+ order
SELECT u.name, COUNT(o.id) AS count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING count >= 3;

-- WHERE vs HAVING
SELECT category_id, AVG(price) AS avg
FROM products
WHERE stock > 0           -- row filter
GROUP BY category_id
HAVING avg > 500;          -- group filter

GROUP_CONCAT

SELECT u.name,
    GROUP_CONCAT(o.id SEPARATOR ',') AS orders
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- করিম | 1,2

Practical

-- মাসিক revenue
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS orders, SUM(total) AS revenue
FROM orders WHERE status='completed'
GROUP BY month ORDER BY month DESC;

-- শীর্ষ ১০ বিক্রীত
SELECT p.name, SUM(oi.quantity) AS sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY sold DESC LIMIT 10;