CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

How by max value to know the value of other column (PostgreSQL)?

I have table in PostgreSQL 10 database.

The table shows the hourly speed data of trains in each underground station of the cities of England.

| STAMPER             | STATION    | CITY      | SPEED    |
|---------------------------------------------------------|
| 2018-10-01 00:00:00 | Arsenal    | London    | 1078.125 |
| 2018-10-01 01:00:00 | Arsenal    | London    | 1524.375 |
| 2018-10-01 02:00:00 | Arsenal    | London    | 1127.752 |
| 2018-10-01 00:00:00 | Beckton    | London    | 2866.375 |
| 2018-10-01 01:00:00 | Beckton    | London    | 877.222  |
| 2018-10-01 02:00:00 | Beckton    | London    | 1618.533 |
| 2018-10-01 00:00:00 | Chesham    | Liverpool | 1567.588 |
| 2018-10-01 01:00:00 | Chesham    | Liverpool | 792.333  |
| 2018-10-01 02:00:00 | Chesham    | Liverpool | 1138.857 |
| 2018-10-01 00:00:00 | Farringdon | Liverpool | 1543.625 |
| 2018-10-01 01:00:00 | Farringdon | Liverpool | 538.666  |
| 2018-10-01 02:00:00 | Farringdon | Liverpool | 1587.583 |

I'm trying to get aggregated data of this kind:

| STAMPER             | CITY      | AVG_SPEED  | MAX_SPEED | MAX_SPEED_STATION |
|----------------------------------------------|-----------|-------------------|
| 2018-10-01 00:00:00 | London    | XXX        | 2866.375  | Beckton           |
| 2018-10-01 01:00:00 | London    | XXX        | 1524.375  | Arsenal           |
| 2018-10-01 02:00:00 | London    | XXX        | 1618.533  | Beckton           |
| 2018-10-01 00:00:00 | Liverpool | XXX        | 1567.588  | Chesham           |
| 2018-10-01 01:00:00 | Liverpool | XXX        | 792.333   | Chesham           |
| 2018-10-01 02:00:00 | Liverpool | XXX        | 1587.583  | Farringdon        |

I have problem with MAX_SPEED_STATION column. In other words, I need in result to see in what station of the city was maximum value of the train speed at certain time.

My currect SQL statement:

SELECT
    A."Stamper" AS DATE_TIME,
    A."CITY" AS city,
    AVG(A.speed) AS AVG_SPEED,
    MAX(A.speed) AS MAX_SPEED,
    MIN(A.speed) AS MIN_SPEED
FROM
    table_name A
GROUP BY
    A."Stamper",
    A."CITY";

Upvotes: 0

Views: 59

Answers (2)

kgzdev
kgzdev

Reputation: 2885

Using subquery:

SELECT groupedA.*, B.STATION as MAX_SPEED_STATION
FROM table_name B
INNER JOIN
    (SELECT 
    A."Stamper" AS DATE_TIME,
    A."CITY" AS city,
    AVG(A.speed) AS AVG_SPEED,
    MAX(A.speed) AS MAX_SPEED,
    MIN(A.speed) AS MIN_SPEED
    FROM table_name A
    GROUP BY 
        A."Stamper",
        A."CITY") groupedA
ON B."Stamper" = groupedA."Stamper" 
AND B.speed = groupedA.MAX_SPEED

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One method is to use a subquery. Another uses array_agg():

SELECT A."Stamper" AS DATE_TIME, A."CITY" AS city,
       AVG(A.speed) AS AVG_SPEED,
       MAX(A.speed) AS MAX_SPEED,
       MIN(A.speed) AS MIN_SPEED,
       ( ARRAY_AGG(A.station ORDER BY A.speed DESC) )[1] as station_at_max_speed
FROM table_name A
GROUP BY A."Stamper", A."CITY";

Upvotes: 2

Related Questions