ruby-on-rails - 结果表中的 Postgresql 总和计数

Question

我有很多地点，人们在逐步过程中处于不同的步骤。我希望能够按位置报告每个步骤的人数，然后报告所有位置的总数。所以我的数据看起来像这样(步骤表)

| ID | STEP_NUM | LOCATION ID |
-------------------------------
| 1  | 1        | 10          |
| 2  | 1        | 12          |
| 3  | 2        | 4           |
| 4  | 1        | 5           |
| 5  | 1        | 6           |
| 6  | 1        | 3           |
| 7  | 3        | 3           |

这个 stackoverflow 问题和答案 Postgresql Multiple counts for one table非常有用，我按位置得到了摘要。这是我当前的查询:

SELECT locations.name,
       sum(case when  step_num = 1 then 1 end) as Beginner,
       sum(case when  step_num = 2 then 1 end) as Intermediate,
       sum(case when  step_num = 3 then 1 end) as Expert       
FROM steps
INNER JOIN locations ON steps.location_id = locations.id
GROUP BY locations.name
ORDER BY locations.name ASC

我还如何返回所有地点的总数？例如我想得到结果:

| LOCATION NAME | Beginner | Intermediate | Expert |
----------------------------------------------------
| Uptown        |   5      |              |    1   |
| Downtown      |   2      |       1      |    3   |
| All locations |   7      |       1      |    4   |

Answer 1

您需要 PostgreSQL 尚不支持的汇总操作，但 can be模拟

WITH location AS (
 SELECT locations.name,
   sum(case when  step_num = 1 then 1 end) as Beginner,
   sum(case when  step_num = 2 then 1 end) as Intermediate,
   sum(case when  step_num = 3 then 1 end) as Expert       
 FROM steps
 INNER JOIN locations ON steps.location_id = locations.id
 GROUP BY locations.name
 ORDER BY locations.name ASC
), total AS (
  SELECT 'Total',
         sum(Beginner),
         sum(Intermediate),
         sum(Expert)
  FROM location
) SELECT *
  FROM location
  UNION ALL
  SELECT *
  FROM total