sql笔记-group by 统计功能

--查询月销售量小于20的人员销售情况
SELECT C.Category,
       SUM(c.[Count]) AS CategorySum
FROM   test.dbo.Category c
       --WHERE c.Category IN ('A','C')
GROUP BY
       C.Category
HAVING SUM(c.[Count]) < 20
ORDER BY
       CategorySum

--查询每门课的最低分
SELECT C.Category,
       MIN(C.[Count]) AS CategoryMinCount
FROM   test.dbo.Category c
GROUP BY
       C.Category


--搜索名字相同的学生
SELECT s.Sname,
       COUNT(s.Sname)
FROM   Stu s
GROUP BY
       s.Sname
HAVING COUNT(s.Sname) > 1


--每个年龄的人数
SELECT 
    s.Age
    ,COUNT(s.Age) AS AgeCount
FROM test.dbo.Stu s
GROUP BY s.Age

--年龄段统计
SELECT t1.AgeRange,
       COUNT(*) AS AgeRangePeopleCount
FROM   (
           SELECT Sno,
                  CASE 
                       WHEN (age > 50) THEN 'gt100'
                       WHEN (age > 20 AND age <= 50) THEN '20-30'
                       WHEN (age <= 20) THEN '10-20'
                  END AS AgeRange
           FROM   test.dbo.Stu s
       ) t1
GROUP BY
       t1.AgeRange