如何获取具有受影响行数的列的总和
我正在尝试获取一列的总和,我也想返回受影响的列数。
例:
orders table
id customer_id name amount
----------------------------------------
1 2 burger 5.00
2 2 pizza 6.00
3 2 grape 1.00
4 1 sandwich 4.00
现在我想对特定客户的金额列求和(customer_id),并返回项目数(计算受影响的行数)
我正在这样做,但它只获得金额的总和,我还想从这个单一查询中获取受影响的行数(计数):
SELECT SUM(amount) AS amount
FROM orders
WHERE customer_id = 2
也做一个COUNT
:
SELECT SUM(amount) AS amount, COUNT(*) AS cnt
FROM orders
WHERE customer_id = 2
如果 amount
是可为空的字段,并且您只想计算NOT NULL
行,则执行COUNT(amount)
而不是 COUNT(*)
。
SELECT SUM(amount) AS amount, COUNT(1) AS cnt
FROM orders
WHERE customer_id = 2