Generating a PostgreSQL query that connects multiple tables and sums certain columns

Question

I'm trying to get the following table returned via a PostgreSQL query:

container length | terminal 1 | terminal 2 | terminal 3 | Cumulative
----------------------------------------------------------------------
10               |   12       |     12     |     12     |     36     |
20               |   34       |     34     |     34     |     102    |
30               |   56       |     56     |     56     |     168    |
40               |   78       |     78     |     78     |     234    |

The numbers are made up but essentially I want to be able to see:

the container length in the first column
all my terminals with the respective number of containers for each container length
the sum of those values (columns 2-4) for each row in column 4.

Is this possible via a SQL query?

The tables are as follows:

Organization
 - terminal_id
 - container_id

Container
 - organization_id
 - current_location_type
 - current_location_id

Terminal
 - organization_id

In other words, an Organization has many containers and has many terminals. A Terminal belongs to an organization. A Container belongs to an organization.

The way in which I can derive a terminal from a container is: containers.current_location_type = 'Terminal' and containers.current_location_id = terminals.id

I have the following SQL query but it's not returning the data in the way that I am looking to return it in the table above.

SELECT containers.container_length AS container_length,
  terminals.name AS terminal_name,
  COUNT(*) AS group_count
FROM containers
LEFT JOIN terminals
  ON terminals.organization_id = organization.id
  AND terminals.id = containers.current_location_id
WHERE containers.organization_id = organization.id
  AND containers.current_location_type = 'Terminal'
GROUP BY containers.container_length, terminals.id
ORDER BY container_length ASC

This query returns 3 columns: container_length, terminal_name and group_count. Seems like I'm on the right path but I haven't been able to find a way to get it as in the table above. Any thoughts on how I might achieve this? Thanks!

Are you able to install extensions? If so you could use `crosstab` to pivot your results. This answer has examples: [PostgreSQL Crosstab Query](https://stackoverflow.com/a/11751905/3591528). — teppic, Aug 18 '18 at 01:28
Do you have always 3 terminal columns or is the number flexible — S-Man, Aug 18 '18 at 08:08
@S-Man it's flexible and is determined by the organization's terminals — DaniG2k, Aug 18 '18 at 08:14
Hm I believe there's no simple solution for flexible columns. You can go with @teppic solution but it needs a fixed number of count columns (with FILTER). As well it is with the crosstab function where you need to name all used columns IMO. You need to do preparing step to calculate your columns. E.g. there's a way to build a query string dynamically in your calling system (or in postgres but it is quiet hard) and call it within a function which converts the string into a query. — S-Man, Aug 18 '18 at 09:05
Another possibility is reporting the terminal counts in JSON. The typing requirements are looser so you wouldn't need to know which terminals you're reporting for in advance. — teppic, Aug 19 '18 at 05:45

score 1 · Answer 1 · answered Aug 18 '18 at 02:46

Since 9.4 postgres has had the ability to apply aggregates selectively with FILTER clauses.

You can do something like this:

SELECT
  containers.container_length,
  count(*) FILTER (WHERE terminals.name = 'terminal_1') AS terminal_1,
  count(*) FILTER (WHERE terminals.name = 'terminal_2') AS terminal_2,
  count(*) FILTER (WHERE terminals.name = 'terminal_3') AS terminal_3,
  count(*) AS cumulative
FROM containers -- ...

You'll have to adjust your grouping to only be by container_length.

Generating a PostgreSQL query that connects multiple tables and sums certain columns

1 Answers1