# 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