SQL Advanced Applications (GROUP by, having)

SQL GROUP by

　　Aggregate functions often need to add a GROUP by statement

The aggregate functions are: AVG (), COUNT (), first (), Last (), MAX (), in (), SUM () ...

The GROUP BY statement is used to combine aggregate functions to group results based on one or more columns

Grammar

SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by column_name

　The following table is an example

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

Example 1, find the total amount per customer (total order), use Group by to group customers

SELECT customer, SUM (Orderprice) from Ordersgroup by Customer

The results are as follows

Customer	SUM (Orderprice)
Bush	2000
Carter	1700
Adams	2000

If you omit GROUP by

SELECT Customer, SUM (orderprice) from Orders

The results are as follows

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

Obviously, this is not what we need.

Why can't I use this SELECT statement above?

Because the SELECT statement above has two columns (Customer and SUM (Orderprice)). SUM (Orderprice) returns a separate value (total for the "orderprice" column), and "Customer" returns 6 values (each corresponding to each row in the Orders table). So we can't get the right results.

So use the group BY statement to solve this problem

GROUP by applies to more than one column

SELECT customer, OrderDate, SUM (Orderprice) from Ordersgroup by Customer, OrderDate

　　

SQL have

　　The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with the aggregate function

Grammar

SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by Column_ Namehaving aggregate_function (column_name) operator value

Example 1, or an example of the above Orders table, to find customers with a total order amount of less than 2000

SELECT Customer, SUM (orderprice) from Ordersgroup by Customerhaving SUM (Orderprice) <2000

The results are similar:

Customer	SUM (Orderprice)
Carter	1700

Example 2, now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

　　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

Results:

Customer	SUM (Orderprice)
Bush	2000
Adams	2000

SQL Advanced Applications (GROUP by, having)