Using SUM OVER without an Order by

  • We capture the MPF at a header level and call it TOTAL_MPF.
    We  also capture Line Level MPF and call it MPF.
    The sum of the Line MPF should equal the TOTAL_MPF. We have some feed issues and a lot of times it does not match.
    I am using
    SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_Sum
    I am not using and ORDER by, because i just want to add up the Line MPF for reach Customs Entry Number. I don't need ORDER by, do i?

    However, i am not getting the same results for the header and line_sum even though they do agree....
    Is there sometIs there something wrong here????

    SELECT H.Customs_Entry_Num,
        h.Total_MPF,
        SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_sum
    FROM ADHOC.ATS_ESH H
      INNER JOIN ADHOC.ATS_ESL L
       ON H.TRANS_SK = L.TRANS_SK
    WHERE H.Importer = 'XXXX'
    and H.Entry_Date > '2/1/2019'
    group by H.Customs_Entry_Num, l.MPF,H.Total_MPF

  • jeffshelix - Thursday, March 28, 2019 10:25 AM

    We capture the MPF at a header level and call it TOTAL_MPF.
    We  also capture Line Level MPF and call it MPF.
    The sum of the Line MPF should equal the TOTAL_MPF. We have some feed issues and a lot of times it does not match.
    I am using
    SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_Sum
    I am not using and ORDER by, because i just want to add up the Line MPF for reach Customs Entry Number. I don't need ORDER by, do i?

    However, i am not getting the same results for the header and line_sum even though they do agree....
    Is there sometIs there something wrong here????

    SELECT H.Customs_Entry_Num,
        h.Total_MPF,
        SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_sum
    FROM ADHOC.ATS_ESH H
      INNER JOIN ADHOC.ATS_ESL L
       ON H.TRANS_SK = L.TRANS_SK
    WHERE H.Importer = 'XXXX'
    and H.Entry_Date > '2/1/2019'
    group by H.Customs_Entry_Num, l.MPF,H.Total_MPF

    Quick thought, you are mixing windowing aggregation and standard aggregation, remove the over clause would be my first suggestion.
    😎

    Suggest you post the DDL (create table) script, sample data as an insert statement and the expected results 😉

  • thanks. I removed the OVER clause, but then i just got the new total without reqard to entry number. We pull from our tables . . here are two Entries ... Column 1 is the Entry. An entry has several Lines. the SUM of the MPF column SHOULD equal the value in the TOTAL_MPF Column. Our data is whacked and it doesnt always match.
    i want to SUM the MPR in column four for each ENTRY in column 1. And then group by ENTRY.
    Thanks again

    entryLine Total_MPF  MPF 
    1234561 $      229.75  $   21.13
    1234562 $      229.75  $   25.03
    1234563 $      229.75  $   27.22
    1234564 $      229.75  $     6.97
    1234565 $      229.75  $     6.97
    1234566 $      229.75  $   19.29
    1234567 $      229.75  $     4.21
    9876541 $      500.00  $   56.00
    9876542 $      500.00  $   11.00
    9876543 $      500.00  $ 115.52
    9876544 $      500.00  $   12.00
    9876545 $      500.00  $     6.97
    9876546 $      500.00  $   19.29
    9876547 $      500.00  $     8.98


     


  • SELECT [Entry],Total_MPF,SUM(MPF) AS [MPF]
    FROM [tablename]
    GROUP BY [Entry],Total_MPF
    HAVING Total_MPF <> SUM(MPF)
    ORDER BY [Entry] ASC;

    From the data you posted returns

    Entry Total_MPF MPF
    123456 229.75 110.82
    987654 500.00 229.76

    What are you expecting different?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • The GROUP BY Entry Num should be enough:


    SELECT H.Customs_Entry_Num,
      MAX(h.Total_MPF) AS Total_MPF,
      SUM(L.MPF) AS Line_Sum
    FROM ADHOC.ATS_ESH H
    INNER JOIN ADHOC.ATS_ESL L
      ON H.TRANS_SK = L.TRANS_SK
    WHERE H.Importer = 'XXXX'
    and H.Entry_Date > '2/1/2019'
    GROUP BY H.Customs_Entry_Num
    --to see only differences, uncomment the next line
    --HAVING MAX(h.Total_MPF) <> SUM(L.MPF)
    --ORDER BY Customs_Entry_Num

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • jeffshelix - Thursday, March 28, 2019 10:48 AM

    thanks. I removed the OVER clause, but then i just got the new total without reqard to entry number. We pull from our tables . . here are two Entries ... Column 1 is the Entry. An entry has several Lines. the SUM of the MPF column SHOULD equal the value in the TOTAL_MPF Column. Our data is whacked and it doesnt always match.
    i want to SUM the MPR in column four for each ENTRY in column 1. And then group by ENTRY.
    Thanks again

    entryLine Total_MPF  MPF 
    1234561 $      229.75  $   21.13
    1234562 $      229.75  $   25.03
    1234563 $      229.75  $   27.22
    1234564 $      229.75  $     6.97
    1234565 $      229.75  $     6.97
    1234566 $      229.75  $   19.29
    1234567 $      229.75  $     4.21
    9876541 $      500.00  $   56.00
    9876542 $      500.00  $   11.00
    9876543 $      500.00  $ 115.52
    9876544 $      500.00  $   12.00
    9876545 $      500.00  $     6.97
    9876546 $      500.00  $   19.29
    9876547 $      500.00  $     8.98


     

    The data does not stack up
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    ;WITH SAMPLE_DATA([entry],Line,Total_MPF,MPF) AS
    (
      SELECT 123456,1,229.75,21.13 UNION ALL
      SELECT 123456,2,229.75,25.03 UNION ALL
      SELECT 123456,3,229.75,27.22 UNION ALL
      SELECT 123456,4,229.75,6.97 UNION ALL
      SELECT 123456,5,229.75,6.97 UNION ALL
      SELECT 123456,6,229.75,19.29 UNION ALL
      SELECT 123456,7,229.75,4.21 UNION ALL
      SELECT 987654,1,500.00,56.00 UNION ALL
      SELECT 987654,2,500.00,11.00 UNION ALL
      SELECT 987654,3,500.00,115.52UNION ALL
      SELECT 987654,4,500.00,12.00 UNION ALL
      SELECT 987654,5,500.00,6.97 UNION ALL
      SELECT 987654,6,500.00,19.29 UNION ALL
      SELECT 987654,7,500.00,8.98
    )

    SELECT
     SD.[entry]
    ,SD.Total_MPF
    ,MAX(SD.Total_MPF) AS MIN_MPF
    ,MIN(SD.Total_MPF) AS MAX_MPF
    ,SUM(SD.MPF) AS CALC_MPF
    FROM SAMPLE_DATA  SD
    GROUP BY SD.[entry]
       ,SD.Total_MPF

  • jeffshelix - Thursday, March 28, 2019 10:25 AM

    We capture the MPF at a header level and call it TOTAL_MPF.
    We  also capture Line Level MPF and call it MPF.
    The sum of the Line MPF should equal the TOTAL_MPF. We have some feed issues and a lot of times it does not match.
    I am using
    SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_Sum
    I am not using and ORDER by, because i just want to add up the Line MPF for reach Customs Entry Number. I don't need ORDER by, do i?

    However, i am not getting the same results for the header and line_sum even though they do agree....
    Is there sometIs there something wrong here????

    SELECT H.Customs_Entry_Num,
        h.Total_MPF,
        SUM(L.MPF) OVER (PARTITION BY H.Customs_Entry_Num) as Line_sum
    FROM ADHOC.ATS_ESH H
      INNER JOIN ADHOC.ATS_ESL L
       ON H.TRANS_SK = L.TRANS_SK
    WHERE H.Importer = 'XXXX'
    and H.Entry_Date > '2/1/2019'
    group by H.Customs_Entry_Num, l.MPF,H.Total_MPF

    Grouping combines several records into a single record on the group criteria.  You have multiple records that have the same l.MPF value, and you are combining them into a single record with that same l.MPF value.  You're essentially removing duplicate l.MPF values.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply