SQL Server Data Query aggregate function and group query

Document directory

• SQL group by syntax
• SQL having syntax

Ii. Aggregate functions and group queries

1. Aggregate functions

Common Aggregate functions include AVG (all/distinct field name), count (all/distinct field name), max (field name), min (field name), and sum (field name)

2. query groups

Group by statement

The group by statement is used in combination with the aggregate function to group result sets based on one or more columns.

SQL group by syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

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

Great, right?

Let's take a look at what will happen if group by is omitted:

SELECT Customer,SUM(OrderPrice) FROM Orders

The result set is similar to the following:

Customer	Sum (orderprice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

The above result set is not what we need.

So why can't I use the preceding SELECT statement? The preceding SELECT statement specifies two columns (customer and sum (orderprice )). "Sum (orderprice)" returns a separate value (total of the "orderprice" column ), "customer" returns six values (each value corresponds to each row in the "orders" table ). Therefore, we cannot get the correct results. However, you can see that the Group by statement solves this problem.

Group by more than one column

You can also apply the group by statement to more than one column, as shown in the following figure:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDate

 

 

Having clause

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

SQL having syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

SQL having 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 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