Group statistics: Count,group by,having, ORDER BY

--statistics on the total number of male and female students
Select COUNT (*) from Student where sex= ' man '
Select COUNT (*) from Student where sex= ' female '
--Count the total number of each class
Select COUNT (*) from Student where classid=1
--Grouping statistics: Need to Group by class, each group get a statistic result
--select field list from table list where data source filter group by Group Field List Order by sort field List
Select ClassID, COUNT (*) from Student GROUP by ClassID
--Use of subgroup statistics total number of male and female students
Select Sex, COUNT (*) from Student GROUP by sex

--The details of the grouping statistics:
Select Sex, COUNT (*) from Student
Select Sex from Student
Select COUNT (*) from Student
--sql the order in which statements are executed
--5 Display 1 2 3 4 6
--select field list from table list where data source filter group by Group Field List Filter order by sort field List
--Grammar rules: Aggregations should not appear in the WHERE clause, the aggregate function should be used in the having
--where is filtering the source data, but having it is filtering the result set obtained by the grouping statistics.
Select ClassID, COUNT (*) as CNT from Student where Address was not null GROUP by CLASSID have COUNT (*) >2 ORDER BY CNT

--Check the total number of male and female students in each class
Select Classid,sex, COUNT (*) from Student GROUP by Classid,sex ORDER by ClassID
--
Select Top 2 * from Student
Select Top 2 * from Student ORDER by Studentname
-When you see each of the different times, consider grouping
--1. Query the total number of hours per class and arrange them in ascending order subject
Select Classid,sum (classhour) from Subject Group by ClassId Order by SUM (Classhour)
--2. Query average result for each participant in the exam
Select (select Studentname from Student where Studentno=result. Studentno), AVG (Studentresult) from Result GROUP by Studentno
--3. Query the average score for each course and arrange the result in descending order
Select Subjectid account ID,AVG (Studentresult) average score from Result GROUP by Subjectid ORDER by account ID Desc
--4. Querying the number of male and female students in each class-multi-field grouping
Select Classid,sex, COUNT (*) from Student GROUP by Classid,sex ORDER by ClassID

Group statistics: Count,group by,having, ORDER BY