sqlserver--aggregation function

--1. Aggregate functions do not count null values

SELECT * FROM Tblstudent

Select COUNT (TSID) from tblstudent

Select AVG (tsage) from Tblstudent--avg () also does not count null values.

Select SUM (tsage) from Tblstudent--sum () for null values, considered to be

--2. If there is no manual group by grouping when using aggregate functions, the aggregate function will count the data in the entire table as a group

----------with conditional query--------------------

--select column

--from table

--where conditions

--inquire about a student who has not passed the exam (hypothesis: math or English, as long as there is a failure to pass the name of a non-pass) number

SELECT * FROM Tblscore

Select Tsid from Tblscore where tenglish<60 or tmath<60

--Search for male students aged between 30 years (inclusive and)

SELECT * FROM Mystudent

SELECT * from Mystudent where fage>=20 and fage<=30 and fgender= ' men '

SELECT * from Mystudent where Fage between and fgender= ' men '

--between...and ... In... Between, (closed interval, contains two endpoint values)

--check for male students aged between 30 years

--Query math score between 90 and all students

SELECT * from Tblscore where tmath between and 90

SELECT * FROM Tblstudent

--Check out all class IDs for those students who are 4,5

--19,1,27,86 SELECT * from Tblstudent where Tsclassid in (19,1,27,86)

SELECT * from tblstudent where tsclassid=3 or tsclassid=4 or tsclassid=5

SELECT * from Tblstudent where Tsclassid in (3,4,5)

--for in or or queries, if the criteria in the query are consecutive numbers, it is best to use >= <= or Between...and do not use or or in. Increase efficiency

SELECT * from Tblstudent where Tsclassid >=3 and tsclassid<=5

sqlserver--aggregation function