是否可以将条件放在预言机的计数函数中?

Question

我有一个带有creditAmount和terminal_ID字段的表，其中我的值是>0，有些是 <0。我想计算每条记录的>0 行和terminal_id。

我试过这个：

SELECT terminal_ID, COUNT(to_number(credit_amount)) AS count 
FROM isevaHarmsReports1 where to_number(credit_amount) > 0 group by  terminal_ID;

如果我执行上述查询，它只给出 0 credit_amount> 的用户计数和terminal_ID，而不是<0

的用户

if credit amount is < 0 I want only terminal_id
if credit amount is > 0 I want only terminal_id and count

如何根据条件计算行数？

Answer 1

试试这个

select terminal_id, 
count(case when credit_amount > 0 then 1 end) as count_gt0,  
count(case when credit_amount < 0 then 1 end) as count_lt0 
from your_table
group by terminal_id

如果credit_amount在null的其他范围内，则case表达式将传递1。聚合函数count不考虑空值。

Answer 2

你可以试试这个：

SELECT terminal_id, COUNT(*)
FROM isevaHarmsReports1
WHERE TO_NUMBER(creditAmount) > 0
GROUP BY terminal_id
UNION
SELECT terminal_id, NULL
FROM isevaHarmsReports1
WHERE TO_NUMBER(creditAmount) < 0
GROUP BY terminal_id