SQL query get sum of sums-CodePudding

Please help me with the problem: I have query:

SELECT drugs_shipment.drug_id,
    drugs_drug.name AS drug_name,
    drugs_drugunit.name AS drug_unit,
  drugs_shipment.initial_amount  - SUM(IFNULL(drugs_movement.amount, "0")) OVER (PARTITION BY drugs_shipment.id) AS total_amount
  FROM drugs_shipment
    JOIN drugs_drug ON drugs_shipment.drug_id = drugs_drug.id
    JOIN drugs_drugunit ON drugs_drug.unit_id = drugs_drugunit.id
    LEFT JOIN drugs_movement ON (drugs_movement.shipment_id = drugs_shipment.id AND drugs_movement.DATE < "2025-12-11" )
WHERE drugs_shipment.date_of_comming < "2025-12-11"
    AND (drugs_shipment.date_of_run_out IS NULL OR drugs_shipment.date_of_run_out > "2025-12-11")

Next, i need sum of total_amount in fields with the same drugs_shipment.drug_id What is whrong? How to solve this problem?

But this doesn't work:

SELECT drugs_shipment.drug_id,
    drugs_drug.name AS drug_name,
    drugs_drugunit.name AS drug_unit,
    SUM (drugs_shipment.initial_amount  - SUM(IFNULL(drugs_movement.amount, "0")) OVER (PARTITION BY drugs_shipment.id)) AS total_amount
FROM drugs_shipment
    JOIN drugs_drug ON drugs_shipment.drug_id = drugs_drug.id
    JOIN drugs_drugunit ON drugs_drug.unit_id = drugs_drugunit.id
    LEFT JOIN drugs_movement ON (drugs_movement.shipment_id = drugs_shipment.id AND drugs_movement.DATE < "2025-12-11" )
WHERE drugs_shipment.date_of_comming < "2025-12-11"
    AND (drugs_shipment.date_of_run_out IS NULL OR drugs_shipment.date_of_run_out > "2025-12-11")
GROUP BY drugs_shipment.drug_id

CodePudding user response：

You are not allowed using window functions (the OVER clause) and grouping (the GROUP BY clause) at the same time. You need to use another window function or nested query to do this.

SELECT drug_id
      ,drug_name
      ,drug_unit
      ,SUM(total_amount) OVER (PARTITION BY drug_id)
FROM
(
    SELECT drugs_shipment.drug_id,
           drugs_drug.NAME AS drug_name,
           drugs_drugunit.NAME AS drug_unit,
           drugs_shipment.initial_amount - Sum(Ifnull(drugs_movement.amount, "0")) OVER (partition BY drugs_shipment.id) AS
           total_amount
    FROM   drugs_shipment
           JOIN drugs_drug
             ON drugs_shipment.drug_id = drugs_drug.id
           JOIN drugs_drugunit
             ON drugs_drug.unit_id = drugs_drugunit.id
           LEFT JOIN drugs_movement
                  ON ( drugs_movement.shipment_id = drugs_shipment.id
                       AND drugs_movement.date < "2025-12-11" )
    WHERE  drugs_shipment.date_of_comming < "2025-12-11"
           AND ( drugs_shipment.date_of_run_out IS NULL
                  OR drugs_shipment.date_of_run_out > "2025-12-11" ) 
) DS