عبارت HAVING SQL

توابع مجموعه‌ای (مثلاً SUM) معمولاً نیاز به استفاده از GROUP BY دارند.

عبارت GROUP BY

استفاده از عبارت GROUP BY برای ترکیب توابع مجموعه‌ای و گروه‌بندی نتایج بر اساس یک یا چند ستون.

قوانین 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 for each customer (total order).

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

Isn't that great, 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 as follows: 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