GROUP BY والدوال التجميعية — SQL الأساسيات

التجميع: GROUP BY والدوال التجميعية

الدوال التجميعية

تحوّل صفوفاً كثيرة إلى قيمة واحدة:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT COUNT(*) AS orders, SUM(total) AS revenue, AVG(total) AS avg_order,
       MIN(total) AS smallest, MAX(total) AS largest
FROM orders WHERE status = 'paid';

| الدالة | ملاحظة |

|---|---|

| COUNT(*) | كل الصفوف |

| COUNT(col) | الصفوف التي فيها col ليست NULL |

| COUNT(DISTINCT col) | القيم المختلفة |

| SUM و AVG | تتجاهل NULL |

GROUP BY: قيمة لكل مجموعة

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT status, COUNT(*) AS n, SUM(total) AS total
FROM orders
GROUP BY status
ORDER BY total DESC;

القاعدة: كل عمود في SELECT إما في GROUP BY أو داخل دالة تجميعية. (SQLite و MySQL القديم يتساهلان ويعيدان قيمة عشوائية من المجموعة — PostgreSQL يرفض. التزم بالقاعدة.)

WHERE مقابل HAVING

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT user_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'          -- يفلتر الصفوف قبل التجميع
GROUP BY user_id
HAVING SUM(total) > 100;       -- يفلتر المجموعات بعد التجميع

WHERE SUM(total) > 100 خطأ: الصفوف تُفلتر قبل وجود المجاميع.

ترتيب التنفيذ الفعلي

FROM / JOIN ← WHERE ← GROUP BY ← HAVING ← SELECT ← ORDER BY ← LIMIT

لهذا لا تستطيع استخدام اسم مستعار من SELECT داخل WHERE (لم يُحسب بعد)، لكن تستطيع في ORDER BY.

التجميع مع الربط

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

SELECT u.city, COUNT(DISTINCT u.id) AS customers, ROUND(SUM(o.total), 2) AS revenue
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.city;

لاحظ: زيد بلا مدينة (NULL) يشكّل مجموعة خاصة إن كان له طلبات.

دوال النوافذ (لمحة)

SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) تحسب مجموعاً تراكمياً دون دمج الصفوف — للترتيب داخل المجموعات والمجاميع المتحركة. مدعومة في PostgreSQL و MySQL 8 و SQLite الحديث.

تمرين

اعرض لكل مستخدم: عدد طلباته، ومتوسط قيمة طلباته المدفوعة، وتاريخ آخر طلب — فقط لمن لديه طلبان أو أكثر.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (id, name, email, city) VALUES
  (1, 'سارة', 'sara@example.com', 'بغداد'),
  (2, 'عمر', 'omar@example.com', 'البصرة'),
  (3, 'ليلى', 'layla@example.com', 'بغداد'),
  (4, 'زيد', 'zaid@example.com', NULL);

INSERT INTO orders (user_id, total, status) VALUES
  (1, 120.0, 'paid'), (1, 45.5, 'paid'), (2, 300.0, 'pending'),
  (3, 80.0, 'paid'), (3, 15.0, 'cancelled'), (1, 60.0, 'pending');

-- إحصائيات عامة
SELECT COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue, ROUND(AVG(total), 2) AS avg_order
FROM orders WHERE status = 'paid';

-- لكل حالة
SELECT status, COUNT(*) AS n, ROUND(SUM(total), 2) AS total
FROM orders GROUP BY status ORDER BY total DESC;

-- العملاء الذين دفعوا أكثر من 100 (WHERE قبل التجميع، HAVING بعده)
SELECT u.name, ROUND(SUM(o.total), 2) AS spent, COUNT(*) AS paid_orders
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.name
HAVING SUM(o.total) > 100
ORDER BY spent DESC;