GROUP by and Count usage detailed

aggregate Functions

Before introducing the group BY and having clauses, we must first talk about a special function in the SQL language: aggregate functions, such as SUM, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally work on more than one record.

SELECT SUM (population) from BBC

The sum action here is on all population fields that return records, and the result is that the query returns only one result, the total population of the country. GROUP by Usage

The GROUP BY statement is understood in the literal sense of the English language as grouping (group) according to certain rules. Its role is to divide a dataset into small areas through certain rules, and then data processing for several small regions.
Note: Group By IS sorted first and then grouped;
For example, if you want to use group by, for example, there is now a table: How many people in each department will use the technology of grouping

Select DepartmentID as ' department name ',
count (*) as ' number ' from Basicdepartment GROUP by DepartmentID

This is using the group by + field for grouping, where we can understand that we grouped the datasets according to the name Id,departmentid of the department, and then how many of the statistics for each group were divided;
Popular Point said: Group By Field 1, Field 2 ... (More than two fields in the entire table) indicates that the data in the dataset is equal, the field 2 is equal, and the data is grouped, showing only one piece of data. Then you can count the field 3 (sum, average, etc.)

Attention
Select Departmentid,departmentname from Basicdepartment GROUP by DepartmentID

– There will be an error

The column ' Departmentname ' in the select list is not valid because the column is not included in an aggregate function or a GROUP by clause. This is what we need to be aware of, if in the return set field, these fields are either to be included in the group by statement, as a basis for grouping, or to be included in aggregate functions . Why, according to the previous note: DepartmentID equal data is grouped together, showing only one record, if there are three data in the dataset.

DepartmentID Departmentname
DEPT001 Technology Department
DEPT001 General Department
DEPT001 Department of Manpower
Then I can only display one record, which I show. I can't tell. Here are three options: Add the Departmentname to the grouping criteria (group by Departmentid,departmentname), and the three records are three groupings. The Departmentname field is not displayed. Combine these three records into a single record count (Departmentname) with aggregate functionswhere and having

The HAVING clause allows us to filter the groups of data after the group. The HAVING clause can use an aggregate function
The WHERE clause filters the records before aggregation. That is, the effect is before the GROUP BY clause and the HAVING clause. Cannot use aggregate function in WHERE clause
An example is provided:
Show the total population number and area of each area.

SELECT region, sum (population), sum (area) from
BBC
GROUP by region

First, you divide the return records into groups by region, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with aggregate functions.

Show the total population number and area of each area. Show only those areas with an area of more than 1000000.

SELECT region, sum (population), sum (area) from
Bbc8 F4 W2 v. (P-f
GROUP by region has
sum (area) >1000000

Here, we cannot use where to filter more than 1000000 of the region because no such record exists in the table. Instead, the HAVING clause allows us to filter the groups of data after the group

Note : When the WHERE clause, the GROUP BY clause, the HAVING clause, and the aggregate function are included, the order of execution is as follows:
Executes the WHERE clause to find data that meets the criteria;
Groups the data by using the GROUP BY clause, the group by which the grouping by clause runs the aggregate function to compute the values of each group, and finally, with the HAVING clause, the group that does not meet the criteria is removed.
Both the HAVING clause and the WHERE clause can be used to set restrictions so that the query results meet certain constraints.
A HAVING clause restricts a group, not a row. Aggregate functions cannot be used in the WHERE clause, and the HAVING clause can. some complex usages of GROUP by and Count

Just use examples to illustrate it.
Existing table: Residential table: DW_DM_RE_RC, some fields are as follows

Select Area_id,area_name,city_id,city_name,rc_id,rc_name,rc_type_id,rc_type_name,rc_address,floor_cnt,building_ CNT from DW_DM_RE_RC

The data are mainly concentrated in the two cities of Suqian and Wuxi.
Now you need to group according to AREA_ID and City_name, and display the amount of data for the same area_id. (area_id and area_name associated, city_id,city_name associated)
First step:
SQL1:

Select COUNT (*) as Count,area_id,area_name,city_id,city_name from DW_DM_RE_RC
Group by area_id,area_name,city_id, City_name

Here count shows the grouping of area_id and city_name conditions,
Area_id=510,city_name= ' Binghu District ' (Wuxi Binghu District) has 131 data indicating that there are 131 data in area_id=510,city_name= ' Yixing ' (Wuxi Yixing District), indicating that area_id=527,city_ Name= ' Sihong ' (Suqian Sihong) has 101 data, but what I need is the total amount of data that belongs to area_id=510 (Wuxi, regardless of which area), and needs to be shown city_name. That is, you need to change the display count value. From this
SQL2:

select T1. Area_id,t1. Area_name,t1. City_id,t1. City_name,t2. Count from (SELECT COUNT (*) as Count,area_id,area_name,city_id,city_name to DW_DM_RE_RC GROUP by area_id,area_name,cit Y_id,city_name) T1 left JOIN (SELECT COUNT (*) as Count,area_id,area_name to DW_DM_RE_RC GROUP by Area_id,area_ NAME) T2 on T1. area_id = T2. AREA_ID and T1. Area_name = T2. Area_name