March 28, 2019 at 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
March 28, 2019 at 10:29 am
jeffshelix - Thursday, March 28, 2019 10:25 AMWe 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 😉
March 28, 2019 at 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 |
March 28, 2019 at 11:05 am
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.
March 28, 2019 at 11:12 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2019 at 11:44 am
jeffshelix - Thursday, March 28, 2019 10:48 AMthanks. 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
March 28, 2019 at 3:38 pm
jeffshelix - Thursday, March 28, 2019 10:25 AMWe 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