Using SUM renders too many records

Have following query that works except for that more than one record is rendered if table has multiple entries. Been looking at this but cannot figure out where the problem is.

The variables in questions are qty_gross and qty_net. the Quantiies are however correct as it i adding up (minus and plus) divided by 42.

Any help will be appreciated. Thank you.

Code:

---

SELECT                CONVERT(CHAR(1), bm.type) AS record_id, 
                COALESCE(sp.petroex_company_code, ' ') AS supplier_code, 
                COALESCE(CONVERT(CHAR(2), MONTH(bm.movement_date)), '  ') AS movement_month,
                COALESCE(CONVERT(CHAR(4), YEAR(bm.movement_date)), '    ') AS movement_year,
                COALESCE(REPLACE(CONVERT(CHAR(10), bm.movement_date, 101), '/', ''), '          ') AS movement_date,
                COALESCE(bm.remarks, ' ') AS trans_descr,
                COALESCE(bm.reference_number, ' ') AS ticket_no,
                  COALESCE(tp.petroex_product_code, ' ') AS petroex_code,
                  COALESCE(bp.tank_code, ' ') AS tank_code,                                                                               
                COALESCE(STR(100E * SUM(bp.gross_quantity / 42E) OVER (PARTITION BY bm.movement_number), 11, 0), '          ') AS qty_gross,
                COALESCE(STR(100E * SUM(bp.net_quantity / 42E) OVER (PARTITION BY bm.movement_number), 11, 0), '          ') AS qty_net,
                  COALESCE(bm.batch_voyage_number, ' ' ) mode_transport,
                    COALESCE(ca.scac_code, ' ') as scac,
                    coalesce(left(case when substring(petroex_fein,3,1) = '-' then stuff(petroex_fein,3,1,'') else petroex_fein end,9),'')
         
FROM                [VEMA-FACS1].FUELFACS.dbo.bulk_movement AS bm
INNER JOIN        [VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id
INNER JOIN        [VEMA-FACS1].FUELFACS.dbo.terminal_product AS tp ON tp.terminal_product_number = bp.terminal_product_number
INNER JOIN        [VEMA-FACS1].FUELFACS.dbo.supplier AS sp ON sp.supplier_number = bp.supplier_number
LEFT JOIN      [VEMA-FACS1].FUELFACS.dbo.carrier as ca ON ca.carrier_number = case when isnumeric(bm.vessel)=1 then convert(int,bm.vessel) end

WHERE                (@DateFrom IS NULL OR bm.movement_date >= @DateFrom) and
                    (@DateTo IS NULL OR bm.movement_date <= @DateTo) and
                tp.product_group_code <> 'ADD' and
                bm.type IN (1, 2) and
                bm.status > 4 and
                bm.reference_number <> ''
order by        bm.movement_date, bm.reference_number

---

Code:

---

Result:

ticket_no        movement_no        petroex_code        qty_gross        qty_net
       
MC 01 497        2191                D87                  883100                    859700
MC 01 497        2191                D87                    883100                      859700
MC 01 497        2191                D87                831000                    859700

They all have same movement number but listed 3 times


bulk_movement table:

movement_id        movement_no

54332                2191       
54355                2191       
54356                2191       


bulk_product table:

movement_id        movement_no                gross_qty        net_qty

54332                TK_17        1                8831                8597
54355                TK_17        1                -8831                -8597
54356                TK_17        1                370902                361074

---

The result set you have listed does not seem complete based on the query you have. Do you have duplicate rows without SUM OVER PARTITION statement?

Yes, I just listed result as it pertains to my issue.

Table: bulk_movement
Field: movement_id and movment_number

Table: bulk_product
Field: movement_id

I need to read all records with a specific movement_number from bulk_movement table (I this case I have 3 of them with same movement_number). Each one of these have a unique movement_id that I need to join on bulk_product table and get the quantity (3 records). The quantity needs to be added together and shown as one movement_id and total quantity.

Hope I make myself clear. Thank you.