查找单个sql查询中销售值大于2000的订单总数和销售总数

Question

INPUT DATA:

Customer_ID order_number    order_value
1                  1      500
1                  2      300
1                  3      2400
1                  4      2123
2                  5      300
2                  2      2400

输出数据:

Customer ID no. of orders   valuegt2000
1             4                   2
2             2                   1

Answer 1

如果您的DBMS支持CASE表达式，您可以使用它。

这个语句至少在PostgreSQL中运行良好:

select
customer_id as "customer id",
count(order_number) as "no. of orders",
sum(
case
when order_value > 2000 then 1
else 0
end
) as valuegt2000
from
my_table
group by
customer_id