计算所有值并计算一些值

Question

我有这张桌子，

uid  order_price
1    10
1    34
1    21
2    16
2    35
2    45

我想要表中的uid计数和订单价格中的uid计数

uid  all  above30
1    3    1
2    3    2

我这样做：

select 
  uid,
  count(1),
  count(case when order_price >= 30 then 1 else 0 )
from table 
group by uid; 

但我得到的结果是：

uid all above30
1   3   3
2   3   3

有什么问题？

Answer 1

尝试：

select 
  uid,
  count(1),
  sum(case when order_price >= 30 then 1 else 0 end)
from table 
group by uid; 

演示：

# select * from t;
 uid | order_price 
-----+-------------
   1 |          10
   1 |          34
   1 |          21
   2 |          16
   2 |          35
   2 |          45
(6 rows)

# select uid, count(1), sum(case when order_price >=30 then 1 else 0 end) from t group by uid order by uid;
 uid | count | sum 
-----+-------+-----
   1 |     3 |   1
   2 |     3 |   2
(2 rows)

Answer 2

这句话：

count(case when order_price >= 30 then 1 else 0 end)

计数所有行，因为所有的case分支返回一个非null值。

count(case when order_price >= 30 then 1 end)

请看演示
结果：

> uid | all | above30
> --: | --: | ------:
>   1 |   3 |       1
>   2 |   3 |       2