Sum function of SQL (mixed with group by and having clauses)

 

 

SQL GROUP BY instance

We have the following "Orders" table:

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 combine the customer.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer

The result set is similar to the following:

Customer	SUM (OrderPrice)
Bush	2000
Carter	1700
Adams	2000

 

Filter data results: The HAVING clause filters the result sets that meet the conditions.

The HAVING clause is added to SQL because the WHERE keyword cannot be used with the aggregate function.

The "Orders" table:

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 customers whose total order amount is less than 2000.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000

The result set is similar:

Customer	SUM (OrderPrice)
Carter	1700

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

We add a common WHERE clause to the SQL statement:

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500

Result set:

Customer	SUM (OrderPrice)
Bush	2000
Adams	2000

More 0