CODEWITHSUNDEEP
sqlpostgresql
README.SVN
README.SVN

Reputation: 1

SQL Query GROUP BY with same values over multiple columns and return SUM of the relative time value

I have pgAdmin 4.16.

The database contains a table called flights. In this table every row represents a flight. When a flight is delayed, the delay codes are used to describe the reason of the delay. Per delay code there is a time delay describing for how long the delay was. A delay can contain up to 3 delay codes with its relative delay time. I can group the delay codes per only 1 set of columns (delay code and delay time), but not over all 3 columns. Here is the script:

SELECT delay_code_1, COUNT(delay_code_1), AVG(delay_time_1), SUM(delay_time_1)
FROM flights
GROUP BY delay_code_1
ORDER BY SUM(delay_time_1) DESC

Here is the flights table:

enter image description here

Here is the desired result:

enter image description here

my sincere thanks

Upvotes: 0

Views: 111

Answers (1)

KenOn10
KenOn10

Reputation: 1968

This problem occurs because the table is not in normal form - repeating groups should be broken out into another table. If this is your schema, you might redisign.

But, assuming you cannot change the schema, one solution would be to union three passes at the table, e.g.

SELECT delay_code, SUM(delay_time) as Total_Time
FROM
(
SELECT delay_code_1 as delay_code, delay_time_1 as delay_time
FROM flight
WHERE delay_code_1 is not null
UNION ALL
SELECT delay_code_2 as delay_code, delay_time_2 as delay_time
FROM flight
WHERE delay_code_2 is not null
UNION ALL
SELECT delay_code_3 as delay_code, delay_time_3 as delay_time
FROM flight
WHERE delay_code_3 is not null
)
GROUP BY delay_code

HTH

EDITED - as mentioned by @a_horse_with_no_name, UNION ALL should be used here. Plain UNION de-dupes the results, so Total_Time would be wrong if there were multiple delays of the same code and time.

Upvotes: 1

Related Questions