SQL Aggregate functions and sorting methods

SQL Aggregate functions and sorting methods

Aggregate functions count, max, min, avg, sum...
Select count (*) from T_Employee
Select Max (FSalary) from T_Employee

Sort ASC ascending DESC descending
Select * from T_Employee order by Fage

Sort by age in descending order. Sort by salary in ascending order if the age is the same
Select * from T_Employee order by FAge DESC, FSalary ASC

Order by must be placed after the where clause

Wildcard Filter
Use like for wildcard Filtering
Single-character wildcard '_'
Multi-character wildcard '%'
Select * from T_Employee where FName like '_ erry'

NULL is unknown, rather than not

SQL ISNULL (), NVL (), IFNULL (), and COALESCE () Functions
See the following "Products" table:

P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 computer 699 25 15
2 printer 365 36
3 telephone 280 159 57

Assume that "UnitsOnOrder" is optional and can contain NULL values.

We use the following SELECT statement:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products
In the preceding example, if the "UnitsOnOrder" value is NULL, the result is NULL.

Microsoft's ISNULL () function is used to specify how to process NULL values.

NVL (), IFNULL () and COALESCE () functions can also achieve the same result.

Here, we want the NULL value to be 0.

If "UnitsOnOrder" is NULL, It is not conducive to calculation. Therefore, if the value is NULL, ISNULL () returns 0.

To query NULL data using SQL statements, use = or <> instead of is NULL or is not NULL.
Select * from T_Employee where FName is NULL

In () matches two values at the same time. Equivalent to 23 or 25

Between 20 and 30 match the number between 20 and 30

The BETWEEN operator is used in the WHERE clause to select a data range BETWEEN two values.
BETWEEN Operator
The BETWEEN operator... AND selects a data range BETWEEN two values. These values can be numerical values, text values, or dates.

SQL BETWEEN Syntax
SELECT column_name (s)
FROM table_name
WHERE column_name
Original BETWEEN value1 AND value2 table (used in the instance :)
P (www.111cn.net) ersons table:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth th Avenue www.111cn. netNew York
3 Carter Thomas Changan Street Beijing
4 Gates Bill Xuanwumen 10 Beijing
BETWEEN operator instance
To display people between "Adams" (included) and "Carter" (excluded) alphabetically, use the following SQL:

SELECT * FROM Persons
WHERE LastName
BETWEEN 'adams' AND 'cart'
Result set:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York

For more details, see: http://www.111cn.net/database/110/BETWEEN-mysql-sql.htm

Group by group

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_name
WHERE column_name operator value
Group by column_name

Select FAge, count (*) from T_Employee
Group by Fage
Divide the same Fage into one group, and then count the number of each group.

The group by clause must be placed after the where clause. If you want to obtain the number of people in a certain age group greater than 1, you cannot use where count (*)> 1, because the aggregate function cannot be placed behind the where clause. Use having clause
Having is used to filter the grouped columns. The columns that can be used are the same as those in select. In the following example, having Fsalary> 2000 can only be used for where Fsalary> 2000.
Select FAge, count (*) from T_Employee
Group by FAge
Having count (*)> 1;

Limit the range of result sets
Select Top 3 * from T_Employee
Order by FSalary DESC

You can use the Row_Number function after selecting 3. 2005 from the sixth name.
Select Top 3 * from T_Employee
Where FNumber not in (select TOP 5 FNumber from T_Employee order by FSalary DESC)
Order by FSalary DESC
From: http://www.111cn.net/database/mssqlserver/39727.htm

How to sort by Aggregate functions in SQL

SELECT clerk, SUM (Quantity) AS total quantity
FROM item
WHERE item = '5'
Group by salesman
Order by sum (Quantity) DESC

SQL aggregate function sorting

This involves assigning aliases first and then assigning aliases.