sql server 2005聚合函数计算

1.计算行数： select count(*) as count  from table1

                 计算某一列中没有NULL值的记录行数：select  count(*) as [total count],count(line1) as [line1 with values] from table1

                 有多少种不同的种类：select count (distinct CardType) as [different credit cards] from table1

2.筛选结果

            where语句筛选查询:

           group by 子句：select city,count(*) as [count of customers] from address group by city

             排序：select city,count(*) as [count of customers] from address group by city  order by city

            当要查询：在客户数量大于50的城市中，每个城市中有多少客户

                           select city , count(*) as [count of customers] from address group by city having (count(*)>50) order by city

3.计算合计

           使用SUM生成合计：  select SUM(LineTotal) as [grand total] from table1

              更清楚的显示每一个产品的合计：select productID,SUM(LineTotal) as [grand total] from table1       Group by productID

             更清楚的显示产品名称：select puduct.productName,   SUM(sales.LineTotal) as [grand total]   from  sales INNER JOIN product  ON sales.productID = product.productID group by product.productName   order by product.productName 通过这个方法还能分的更细，以下不在写出

          使用ROLLUP来计算小计：通过在group by子句中加入with ROLLUP操作符来显示每一列的小计

            例如：select C.Name as category,S.Name as subcategory

                        SUM(O.LineTotal) as sales

                       from  sales.salesOrderDetail as O INNER JOIN production.product as P ON O.productID= P.productID

                                                                                       INNER JOIN production.productsubcategory as S ON P.productsubcategoryID=S.productsubcategoryID

                                                                                      INNER JOIN production.productcategory as C ON S.productcategoryID=C.productcategoryID

                      group by C.name,S.name WHIT ROLLUUP

                      order by category,subcategory

              结果显示：总的销售量，分类category的销量及子分类subcategory的销量。

            通过使用GROUPING区别合计行和明细行：还有排序和CASE会使更清晰的显示

             select C.Name as category,S.Name as subcategory

                        SUM(O.LineTotal) as sales

                         GROUPING(C.name) as iscategorygroup

                        GROUPING(S.name) as issubcategorygroup

                       from  sales.salesOrderDetail as O INNER JOIN production.product as P ON O.productID= P.productID

                                                                                       INNER JOIN production.productsubcategory as S ON P.productsubcategoryID=S.productsubcategoryID

                                                                                      INNER JOIN production.productcategory as C ON S.productcategoryID=C.productcategoryID

                      group by C.name,S.name WHIT ROLLUUP

                      order by category,subcategory