در SQL، می‌توانید از دستور 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(۱۰,۲) NOT NULL
);
۱. محاسبه مجموع فروش هر محصول:
SELECT product_id, SUM(price * quantity) AS total_sales
FROM orders
GROUP BY product_id;
خروجی:
| product_id | total_sales |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام مجموع فروش) |
| (ردیف دوم – شناسه محصول) | (ارقام مجموع فروش) |
| … | … |
۲. محاسبه میانگین قیمت هر محصول:
SELECT product_id, AVG(price) AS average_price
FROM orders
GROUP BY product_id;
خروجی:
| product_id | average_price |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام میانگین قیمت) |
| (ردیف دوم – شناسه محصول) | (ارقام میانگین قیمت) |
| … | … |
۳. محاسبه تعداد کل سفارشات برای هر مشتری:
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(۱۰,۲) NOT NULL,
customer_city VARCHAR(۵۰) NOT NULL
);
۱. محاسبه مجموع فروش محصولات در سال ۱۴۰۲ و تفکیک آن بر اساس شهر مشتریان:
SELECT customer_city, SUM(price * quantity) AS total_sales
FROM orders
WHERE order_date BETWEEN '۲۰۲۳-۰۳-۲۱' AND '۲۰۲۴-۰۳-۲۰'
GROUP BY customer_city;
خروجی:
| customer_city | total_sales |
|---|---|
| تهران | (ارقام مجموع فروش در تهران) |
| مشهد | (ارقام مجموع فروش در مشهد) |
| … | … |
۲. محاسبه میانگین قیمت و حداقل و حداکثر قیمت هر محصول با تعداد فروش بیش از ۱۰۰:
SELECT product_id, AVG(price) AS average_price, MIN(price) AS min_price, MAX(price) AS max_price
FROM orders
WHERE quantity > ۱۰۰
GROUP BY product_id;
خروجی:
| product_id | average_price | min_price | max_price |
|---|---|---|---|
| (ردیف اول – شناسه محصول) | (ارقام میانگین قیمت) | (ارقام کمترین قیمت) | (ارقام |
بیشترین قیمت) | | (ردیف دوم – شناسه محصول) | (ارقام میانگین قیمت) | (ارقام کمترین قیمت) | (ارقام بیشترین قیمت) | | … | … | … | … |
۳. محاسبه تعداد و میانگین مبلغ سفارشات برای هر محصول در هر ماه سال ۱۴۰۲:
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 '۲۰۲۳-۰۳-۲۱' AND '۲۰۲۴-۰۳-۲۰'
GROUP BY product_id, MONTH(order_date);
خروجی:
| product_id | month | total_orders | average_order_value |
|---|---|---|---|
| (ردیف اول – شناسه محصول) | ۳ | (ارقام تعداد کل سفارشات) | (ارقام میانگین |
مبلغ فاکتور) | | (ردیف دوم – شناسه محصول) | ۴ | (ارقام تعداد کل سفارشات) | (ارقام میانگین مبلغ فاکتور) | | … | … | … | … |
۴. پیدا کردن محصولاتی که در هیچ سفارشی در شهر تهران فروخته نشده‌اند:
SELECT product_id
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM orders
WHERE customer_city = 'تهران'
);
خروجی:
| product_id |
|---|
| (ردیف اول – شناسه محصول) |
| (ردیف دوم – شناسه محصول) |
| … |
۵. محاسبه میانگین مبلغ فاکتور برای هر فروشنده (با استفاده از شناسه کاربری منحصر به فرد فروشنده):
SELECT seller_id, AVG(price * quantity) AS average_order_value
FROM orders
GROUP BY seller_id;
۶. محاسبه درصد سهم هر محصول از کل فروش:
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 |
|---|---|
| (ردیف اول – شناسه محصول) | (ارقام درصد سهم) |
| (ردیف دوم – شناسه محصول) | (ارقام درصد سهم) |
| … | … |
۷. یافتن محصولاتی که در هیچ سفارشی در یک ماه خاص (مثلاً فروردین ۱۴۰۲) فروخته نشده‌اند:
SELECT product_id
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM orders
WHERE MONTH(order_date) = ۳
);
خروجی:
| product_id |
|---|
| (ردیف اول – شناسه محصول) |
| (ردیف دوم – شناسه محصول) |
| … |
۸. محاسبه تعداد مشتریان جدید در هر ماه سال ۱۴۰۲:
SELECT MONTH(order_date) AS month, COUNT(DISTINCT customer_id) AS new_customers
FROM orders
WHERE order_date BETWEEN '۲۰۲۳-۰۳-۲۱' AND '۲۰۲۴-۰۳-۲۰'
GROUP BY MONTH(order_date);
خروجی:
| month | new_customers |
|---|---|
| ۳ | (ارقام تعداد مشتریان جدید) |
| ۴ | (ارقام تعداد مشتریان جدید) |
| … | … |
۹. یافتن مشتریانی که بیشترین خرید را در سال ۱۴۰۲ انجام داده‌اند:
SELECT customer_id, SUM(price * quantity) AS total_sales
FROM orders
WHERE order_date BETWEEN '۲۰۲۳-۰۳-۲۱' AND '۲۰۲۴-۰۳-۲۰'
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT ۱۰;
خروجی:
| customer_id | total_sales |
|---|
| (ردیف اول – شناسه مشتری با بیشترین خرید) | (ارقام مجموع خرید) | | (ردیف دوم – شناسه مشتری با دومین خرید) | (ارقام مجموع خرید) | | … | … |
۱۰. محاسبه میانگین مبلغ فاکتور برای هر محصول و تفکیک آن بر اساس جنسیت مشتریان:
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 | (ارقام میانگین مبلغ فاکتور) | | … | … | … |
