SQL คำสั่ง GROUP BY

গণনা ফাংশন (যেমন SUM) সাধারণত GROUP BY বিন্যাসকে যুক্ত করতে হয়

GROUP BY বিন্যাস

GROUP BY বিন্যাসটি গণনা ফাংশন (যেমন SUM) জুড়ে দেওয়ার জন্য ব্যবহৃত হয়, একটি বা একাধিক কলামের ভিত্তিতে ফলাফল সেটকে গ্রুপ করে

SQL GROUP BY বিন্যাস

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY একটি উদাহরণ

আমরা নিচের এই "Orders" টেবিলটি মালিকানা করি:

O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

Now, we want to find the total amount (total order) for each customer.

We want to use the GROUP BY statement to group customers.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result set is similar to this:

Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000

Cool, isn't it?

Let's see what happens if we omit GROUP BY:

SELECT Customer, SUM(OrderPrice) FROM Orders

The result set is similar to this:

Customer SUM(OrderPrice)
Bush 5700
Carter 5700
Bush 5700
Bush 5700
Adams 5700
Carter 5700

The above result set is not what we need.

Why can't we use the above SELECT statement? Explanation: The above SELECT statement specifies two columns (Customer and SUM(OrderPrice)). "SUM(OrderPrice)" returns a single value (the total of the "OrderPrice" column), while "Customer" returns 6 values (each corresponding to each row in the "Orders" table). Therefore, we do not get the correct result. However, as you have seen, the GROUP BY statement solves this problem.

GROUP BY More Than One Column

We can also apply the GROUP BY statement to more than one column, like this:

SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders
GROUP BY Customer, OrderDate