Postgresql Sum Counts from Results Table

Question

I have a number of locations with people that are at various steps in a step-wise process. I'd like to be able to report the count of people at each step by location and then the total for all locations. So my data looks like this (steps table)

| 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           |

This stackoverflow question and answer(s) Postgresql Multiple counts for one table was very useful and I got the summary by location. Here is my current query:

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

How would I also return the total for all locations? For example I would like to get the result:

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

Community · Accepted Answer · 2017-05-23 11:50:06Z

1

You need rollup operation which is not supported in PostgreSQL yet but can be emulated

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

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Nov 4, 2013 at 13:01

Roman-Stop RU aggression in UA

16k3 gold badges52 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JHo Over a year ago

Thank you @roman-konoval. This looks like the right ingredients but I am getting an unhelpful error from pgAdmin. ERROR: failed to find conversion function from unknown to character varying

Roman-Stop RU aggression in UA Over a year ago

Check that column order is the same for both selects

JHo Over a year ago

I think column order is correct. Is it possible there's a type mismatch or string type mismatch? I noticed if I remove the SELECT * ... UNION ALL I get the total row and it is numeric whereas the "location" result is bigInt. Also, I added as Columnname to each sum(Beginner) as Beginner, ... but that did not fix it either. Same error message. So the Union seems broken for some reason.

JHo Over a year ago

Adding ::text to make SELECT 'Total'::text fixed the issue. Thanks.

Collectives™ on Stack Overflow

Postgresql Sum Counts from Results Table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related