در SQL، می‌توانید از دستور GROUP BY برای گروه بندی اطلاعات بر اساس یک یا چند ستون و سپس انجام محاسباتی مانند جمع، میانگین، حداقل و حداکثر بر روی هر گروه استفاده کنید.
مثال1:
فرض کنید جدول زیر را برای ذخیره اطلاعات فروش محصولات در یک فروشگاه آنلاین داریم:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL
);
1. محاسبه مجموع فروش هر محصول:
SELECT product_id, SUM(price * quantity) AS total_sales
FROM orders
GROUP BY product_id;
خروجی:
| product_id | total_sales |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام مجموع فروش) |
| (ردیف دوم – شناسه محصول) | (ارقام مجموع فروش) |
| … | … |
2. محاسبه میانگین قیمت هر محصول:
SELECT product_id, AVG(price) AS average_price
FROM orders
GROUP BY product_id;
خروجی:
| product_id | average_price |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام میانگین قیمت) |
| (ردیف دوم – شناسه محصول) | (ارقام میانگین قیمت) |
| … | … |
3. محاسبه تعداد کل سفارشات برای هر مشتری:
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;
خروجی:
| customer_id | total_orders |
|---|---|
| (ردیف اول – شناسه مشتری) | (ارقام تعداد کل سفارشات) |
| (ردیف دوم – شناسه مشتری) | (ارقام تعداد کل سفارشات) |
| … | … |
نکات:
- می‌توانید از توابع جمع‌آوری (aggregation) مختلف مانند
SUM,AVG,MIN,MAXدرGROUP BYاستفاده کنید. - می‌توانید از چند ستون در
GROUP BYبا استفاده از,استفاده کنید.
فرض کنید جدول زیر را برای ذخیره اطلاعات فروش محصولات در یک فروشگاه آنلاین داریم:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
customer_city VARCHAR(50) NOT NULL
);
1. محاسبه مجموع فروش محصولات در سال 1402 و تفکیک آن بر اساس شهر مشتریان:
SELECT customer_city, SUM(price * quantity) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY customer_city;
خروجی:
| customer_city | total_sales |
|---|---|
| تهران | (ارقام مجموع فروش در تهران) |
| مشهد | (ارقام مجموع فروش در مشهد) |
| … | … |
2. محاسبه میانگین قیمت و حداقل و حداکثر قیمت هر محصول با تعداد فروش بیش از 100:
SELECT product_id, AVG(price) AS average_price, MIN(price) AS min_price, MAX(price) AS max_price
FROM orders
WHERE quantity > 100
GROUP BY product_id;
خروجی:
| product_id | average_price | min_price | max_price |
|---|---|---|---|
| (ردیف اول – شناسه محصول) | (ارقام میانگین قیمت) | (ارقام کمترین قیمت) | (ارقام |
بیشترین قیمت) | | (ردیف دوم – شناسه محصول) | (ارقام میانگین قیمت) | (ارقام کمترین قیمت) | (ارقام بیشترین قیمت) | | … | … | … | … |
3. محاسبه تعداد و میانگین مبلغ سفارشات برای هر محصول در هر ماه سال 1402:
SELECT product_id, MONTH(order_date) AS month, COUNT(*) AS total_orders, AVG(price * quantity) AS average_order_value
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY product_id, MONTH(order_date);
خروجی:
| product_id | month | total_orders | average_order_value |
|---|---|---|---|
| (ردیف اول – شناسه محصول) | 3 | (ارقام تعداد کل سفارشات) | (ارقام میانگین |
مبلغ فاکتور) | | (ردیف دوم – شناسه محصول) | 4 | (ارقام تعداد کل سفارشات) | (ارقام میانگین مبلغ فاکتور) | | … | … | … | … |
4. پیدا کردن محصولاتی که در هیچ سفارشی در شهر تهران فروخته نشده‌اند:
SELECT product_id
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM orders
WHERE customer_city = 'تهران'
);
خروجی:
| product_id |
|---|
| (ردیف اول – شناسه محصول) |
| (ردیف دوم – شناسه محصول) |
| … |
5. محاسبه میانگین مبلغ فاکتور برای هر فروشنده (با استفاده از شناسه کاربری منحصر به فرد فروشنده):
SELECT seller_id, AVG(price * quantity) AS average_order_value
FROM orders
GROUP BY seller_id;
6. محاسبه درصد سهم هر محصول از کل فروش:
SELECT product_id, (SUM(price * quantity) / total_sales) AS percentage_share
FROM orders
CROSS JOIN (
SELECT SUM(price * quantity) AS total_sales
FROM orders
) AS total_sales_table
GROUP BY product_id;
خروجی:
| product_id | percentage_share |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام درصد سهم) |
| (ردیف دوم – شناسه محصول) | (ارقام درصد سهم) |
| … | … |
7. یافتن محصولاتی که در هیچ سفارشی در یک ماه خاص (مثلاً فروردین 1402) فروخته نشده‌اند:
SELECT product_id
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM orders
WHERE MONTH(order_date) = 3
);
خروجی:
| product_id |
|---|
| (ردیف اول – شناسه محصول) |
| (ردیف دوم – شناسه محصول) |
| … |
8. محاسبه تعداد مشتریان جدید در هر ماه سال 1402:
SELECT MONTH(order_date) AS month, COUNT(DISTINCT customer_id) AS new_customers
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY MONTH(order_date);
خروجی:
| month | new_customers |
|---|---|
| 3 | (ارقام تعداد مشتریان جدید) |
| 4 | (ارقام تعداد مشتریان جدید) |
| … | … |
9. یافتن مشتریانی که بیشترین خرید را در سال 1402 انجام داده‌اند:
SELECT customer_id, SUM(price * quantity) AS total_sales
FROM orders
WHERE order_date BETWEEN '2023-03-21' AND '2024-03-20'
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 10;
خروجی:
| customer_id | total_sales |
|---|
| (ردیف اول – شناسه مشتری با بیشترین خرید) | (ارقام مجموع خرید) | | (ردیف دوم – شناسه مشتری با دومین خرید) | (ارقام مجموع خرید) | | … | … |
10. محاسبه میانگین مبلغ فاکتور برای هر محصول و تفکیک آن بر اساس جنسیت مشتریان:
SELECT product_id, customer_gender, AVG(price * quantity) AS average_order_value
FROM orders
GROUP BY product_id, customer_gender;
خروجی:
| product_id | customer_gender | average_order_value |
|---|---|---|
| (ردیف اول – شناسه محصول) | M | (ارقام میانگین |
مبلغ فاکتور) | | (ردیف دوم – شناسه محصول) | F | (ارقام میانگین مبلغ فاکتور) | | … | … | … |
