CODEWITHSUNDEEP
ruby-on-railspostgresql
DaniG2k
DaniG2k

Reputation: 4903

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

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:

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!

Upvotes: 2

Views: 58

Answers (1)

teppic
teppic

Reputation: 7286

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.

Upvotes: 1

Related Questions