Как обойти добавление полей в GROUP BY

Question

Я пишу SQL запрос. Я прикрепил изображение, какую таблицу мне нужно получить. Вот запрос, который я написал:

SELECT
    MEDICINE.RELEASE_FORM AS 'Группа', 
    MEDICINE.NAME AS 'Товар',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 1 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Январь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 1 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 1 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 2 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Февраль',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 2 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 2 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 3 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Март',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 3 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 3 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 4 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Апрель',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 4 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 4 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 5 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Май',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 5 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 5 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 6 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Июнь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 6 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 6 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 7 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Июль',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 7 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 7 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 8 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Август',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 8 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 8 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 9 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Сентябрь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 9 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 9 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 10 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Октябрь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 10 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 10 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 11 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Ноябрь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 11 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 11 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group',

    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 12 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) AS 'Декабрь',
    SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 12 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.NAME) / 
    NULLIF(SUM(CASE WHEN MONTH(ORDERS.DATE_ORDER) = 12 THEN COMPOSITION_OF_ORDER.PRICE * COMPOSITION_OF_ORDER.QUANTITY ELSE 0 END) OVER (PARTITION BY MEDICINE.RELEASE_FORM), 0) AS '% in group'

FROM MEDICINE
INNER JOIN COMPOSITION_OF_ORDER
ON MEDICINE.ID_MEDICINE = COMPOSITION_OF_ORDER.MEDICINE_ID
INNER JOIN ORDERS
ON COMPOSITION_OF_ORDER.ORDER_ID = ORDERS.ID_ORDER
GROUP BY MEDICINE.NAME, MEDICINE.RELEASE_FORM, COMPOSITION_OF_ORDER.PRICE, COMPOSITION_OF_ORDER.QUANTITY, ORDERS.DATE_ORDER;

Из-за того, что мне пришлось добавить поля COMPOSITION_OF_ORDER.PRICE, COMPOSITION_OF_ORDER.QUANTITY, ORDERS.DATE_ORDER я получаю запрос с множество лишних строк (также прикрепил изображение того, что получается при выполнении запроса). Как можно избежать этого. Думал, что можно как-то с помощью with и разделял запрос, но все равно требует группировку по этим же полям.

БЛОГ НА HUSL

Как обойти добавление полей в GROUP BY

Ответы (0 шт):