Using SUM OVER without an Order by

  • jeffshelix

    SSCrazy

    Points: 2235

    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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    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 😉

  • jeffshelix

    SSCrazy

    Points: 2235

    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

    entry Line  Total_MPF   MPF 
    123456 1  $      229.75  $   21.13
    123456 2  $      229.75  $   25.03
    123456 3  $      229.75  $   27.22
    123456 4  $      229.75  $     6.97
    123456 5  $      229.75  $     6.97
    123456 6  $      229.75  $   19.29
    123456 7  $      229.75  $     4.21
    987654 1  $      500.00  $   56.00
    987654 2  $      500.00  $   11.00
    987654 3  $      500.00  $ 115.52
    987654 4  $      500.00  $   12.00
    987654 5  $      500.00  $     6.97
    987654 6  $      500.00  $   19.29
    987654 7  $      500.00  $     8.98


     

  • David Burrows

    SSC Guru

    Points: 64474


    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.

  • ScottPletcher

    SSC Guru

    Points: 98053

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    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

    entry Line  Total_MPF   MPF 
    123456 1  $      229.75  $   21.13
    123456 2  $      229.75  $   25.03
    123456 3  $      229.75  $   27.22
    123456 4  $      229.75  $     6.97
    123456 5  $      229.75  $     6.97
    123456 6  $      229.75  $   19.29
    123456 7  $      229.75  $     4.21
    987654 1  $      500.00  $   56.00
    987654 2  $      500.00  $   11.00
    987654 3  $      500.00  $ 115.52
    987654 4  $      500.00  $   12.00
    987654 5  $      500.00  $     6.97
    987654 6  $      500.00  $   19.29
    987654 7  $      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

  • drew.allen

    SSC Guru

    Points: 76493

    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 7 (of 7 total)

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