SQL review-Grouping

--Data Grouping-statistical information--total number of queries per classSelect COUNT(8) fromStudentSelectClassId,COUNT(*) fromStudentGroup  byClassId--get the total number of boys, girls group by by the specified field categorySelectSexCOUNT(*) fromStudentGroup  bySex--other columns that appear in the query along with the aggregation function have only two possibilities: they are aggregated, grouped, and are often groupedSelectSexCOUNT(*) fromStudentSelect distinctSex fromStudentSelect COUNT(*) fromStudentSelectClassIDCOUNT(*) fromStudentSelect *  fromStudent--order cannot be changed select (5) Top/distinct 7 query field from (1) Table list where (2) filter the source Data group by (3) Grouping statistic field list having (4) filtering the grouping statistic result set order by (6) record rearrangement of final result set--where is the filtering of the source data. It can only use the columns specified in the table following the from--aggregations should not appear in the WHERE clause, meaning that the condition of the aggregate function cannot be specified by where--If you are filtering the result set after grouping, you need to use the having--The having column is obtained from group by--The grouping statistic result set is filtered before the data is displayed. SelectClassId,COUNT(*) num fromStudentGroup  byClassIdOrder  byNumdescSelect Top 2ClassId,COUNT(*) num fromStudentGroup  byClassIdOrder  byNumdesc--1. Query the total number of hours per class and in ascending order--2. Check the average score for each student taking the exam--3. Check the average score for each course and arrange it in descending order--4. Check the number of male and female students in each class--when you see something like: individual, individual, each, different these words need to consider groupingSelectClassId,SUM(Classhour) fromSubjectwhereClassId is  not NULL Group  byClassIdSelectStudentno,AVG(Studentresult) fromResultGroup  byStudentnoSelectSubjectid,AVG(Studentresult) fromResultGroup  bySubjectid--Check the number of male and female students in each classSelectClassid,sex,COUNT(*) fromStudentGroup  byClassid,sexOrder  byClassid,sex

SQL review-Grouping