Подведение итогов таблицы Mysql с использованием подзапроса

У меня есть простая таблица mysql, которую я хочу обобщить. Вот мой стол

Имя таблицы: hauling_trip

Столбцы:

ID, CONTRACTOR, HAULING_SHEET_NO, FROM_LOCATION, TO_LOCATION, LOAD_WEIGHT_KG, PASS_NO

Данные:

1   PM  A00001  Stumping A  Pangkalan   940.00  112233
2   PM  A00002  Stumping B  Pangkalan   530.00  112233
3   PM  A00003  Stumping B  Pangkalan   970.00  112244
4   PM  A00004  Stumping A  Pangkalan   450.00  112244
5   TKF A00005  Stumping A  Pangkalan   850.00  112255
6   TKF A00006  Stumping B  Pangkalan   780.00  112255
7   TKF A00007  Stumping A  Pangkalan   903.00  112266

Что я хочу сделать, так это обобщить эту таблицу, указав общее количество поездок и вес груза (кг) для каждого PASS_NO. Вот желаемый результат

PASS_NO CONTRACTOR  WEIGHT_A    TRIP_A  WEIGHT_B    TRIP_B
112233  PM          940.00      1       530.00      0
112244  PM          450.00      0       970.00      1
112255  TKF         850.00      1       780.00      0
112266  TKF         903.00      1       0.00        0

Идентичный PASS_NO означает, что перевозка листов считается за 1 поездку. Например, ведомости перевозки A00001 и A00002 имеют одинаковый PASS_NO, который равен 112233. Таким образом, обе ведомости перевозки относятся к 1 поездке, И эта поездка принадлежит FROM_LOCATION Пенька А, потому что вес больше, чем Пень Б.

Вот запрос, над которым я сейчас работаю. Но я не могу понять, как правильно организовать поездку.

SELECT
    a.PASS_NO,
    a.CONTRACTOR,
    ( SELECT sum( LOAD_WEIGHT_KG ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping A' ) AS WEIGTH_A,
    ( SELECT count( PASS_NO ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping A' ) AS TRIP_A,
    ( SELECT sum( LOAD_WEIGHT_KG ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping B' ) AS WEIGHT_B,
    ( SELECT count( PASS_NO ) FROM hauling_trip WHERE PASS_NO = a.PASS_NO AND FROM_LOCATION = 'Stumping B' ) AS TRIP_B, 
    SUM(a.LOAD_WEIGHT_KG) AS TOTAL_WEIGHT
FROM
    hauling_trip AS a 
GROUP BY
    a.PASS_NO, a.CONTRACTOR

🤔 А знаете ли вы, что...
MySQL активно разрабатывается и обновляется с целью улучшения функциональности и безопасности.


31
1

Ответ:

Используйте условную агрегацию:

SELECT ht.PASS_NO, ht.CONTRACTOR,
       sum(case when ht.from_location = 'Stumping A' then ht.load_weight_kg end) as weight_a,
       sum(ht.from_location = 'Stumping A') as trip_a,
       sum(case when ht.from_location = 'Stumping B' then ht.load_weight_kg end) as weight_b,
       sum(ht.from_location = 'Stumping B') as trip_b
       sum(ht.LOAD_WEIGHT_KG) AS TOTAL_WEIGHT
FROM hauling_trip ht
GROUP BY ht.PASS_NO, ht.CONTRACTOR