关于sql sum,case when.....end , count 的应用

category | commdity | price    |num
 ------------+------------+--------+------
  衣服        | T恤        |  1000  | 1
  办公用品 | 打孔器   |   500   | 3 
  厨房用具 | 菜刀       |  3000  | 3
  厨房用具 | 高压锅    |  6800  |4
  厨房用具 | 叉子       |   500   | 2 
  厨房用具 | 擦菜板    |   880   | 5
  办公用品 | 圆珠笔    |   100   | 6
  衣服        | 运动T恤  |  4000  |  0

1. 如果我想把商品按照1000元以下，1000-3000, 3000 以上分为三个档次显示出来，正确的语句是：

SELECT SUM (CASE WHEN price <= 1000 THEN 1 ELSE 0 END)AS low,

	SUM (CASE WHEN price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END)AS mid,
	SUM (CASE WHEN price > 3000 THEN 1 ELSE 0 END)AS high 

FROM TEST;

2. 如果我想把商品按照1000元以下，1000-3000, 3000 以上 总价值；正确的语句是：
select  sum(case when price<100  then nvl(price,0.00)*nvl(num,0) end ) as lowtotalprice ,

		sum(case when price BETWEEN 1001 AND 3000 then nvl(price,0.00)*nvl(num,0.00) end ) as normaltotalprice ,		

		sum(case when price<100  then nvl(price,0.00)*nvl(num,0) end ) as hightotalprice

from  TEST;

3. 使用count函数，计算 3个档次的数目，正确的语句是：

SELECT COUNT(price) FROM TEST WHERE price <= 1000 

特别感谢 
云朵的编程日记

 http://blog.csdn.net/u012531272/article/details/50295397