ruby-on-rails - rails + PostgreSQL : grouping together with a sum

Question

我有一个具有这种结构的表:

id | name      | batch_id | used
----------------------------------
1  | voucher 1 | 1        | 1
2  | voucher 2 | 1        | 0
3  | voucher 3 | 1        | 1
4  | voucher 4 | 2        | 0
5  | voucher 5 | 3        | 1

我需要运行一个查询，该查询将按 batch_id 分组并对使用的列求和。

所以我需要的结果是:

batch_id | sum_used
----------------------------------
1        | 2
2        | 0
3        | 1

这是我目前所拥有的:

TableName.select('DISTINCT ON (batch_id) batch_id')

这与:

SELECT DISTINCT ON (batch_id) batch_id FROM table_name

分组有效，但我无法求和。

谁能指出我正确的方向？

Answer 1

需要使用GROUP BY和SUM聚合函数:

SELECT batch_id, SUM(used::int) AS sum_used
FROM table_name
GROUP BY batch_id
ORDER BY batch_id;

SqlFiddleDemo

输出:

╔═══════════╦══════════╗
║ batch_id  ║ sum_used ║
╠═══════════╬══════════╣
║        1  ║        2 ║
║        2  ║        0 ║
║        3  ║        1 ║
╚═══════════╩══════════╝

---

或者使用窗口函数:

SELECT DISTINCT batch_id, SUM(used::int) OVER(PARTITION BY batch_id) AS sum_used
FROM table_name
ORDER BY batch_id

SqlFiddleDemo2