All Forums Database
Albright 8 posts Joined 07/11
01 Jul 2011
SQL help in summing only selected records based on max value within group

Here is my data …

Month Tran-Code Qty
4 100 48
4 300 30
5 300 42
6 300 44

Here are my desired results …

Month Tran-Code Qty
4 100 48
6 300 44

I tried this:

SELECT Month, Tran-Code, MAX(Month), SUM (Qty)
FROM table
GROUP BY 1, 2;

and got the following …

Month Tran-Code Qty
4 100 48
6 300 116 < - - - wrong (this is 30 + 42 + 44)

The problem is that the sum is happening on the ENTIRE data, and is simply displays the max date from all of the records summed. What I need is to limit each Month/Tran-Cd combination to just the most recent date and THEN sum.

Albright 8 posts Joined 07/11
01 Jul 2011

My simplistic example hides the fact I do need to sum the quantities, not just select the records. But if I could figure out how to just select them, I could imbed that select and then sum them (???).

Albright 8 posts Joined 07/11
01 Jul 2011

So here is a more comlplete example.

My data …

Month Tran-Code Qty
4 100 48
4 300 30
5 300 42
6 300 44
6 300 10

My desired results …

Month Tran-Code Qty
4 100 48
6 300 54 < - - - correct (this is 44 + 10)

My actual results ...

Month Tran-Code Qty
4 100 48
6 300 126 < - - - wrong (this is 30 + 42 + 44 + 10)

Jimm 298 posts Joined 09/07
01 Jul 2011

Select D1.MMonth As Month, D1.Tran_Code, Sum(T1.Qty) As Qty
From
(Select Tran_Code, Max(Month) as MMonth
From Table
Group By 1) As D1
Join Table T1
On D1.Tran_Code = T1.Tran_Code
And D1.MMonth = t1.Month
Group By 1,2
;

It will take 2 passes of the table!

dnoeth 2698 posts Joined 11/04
02 Jul 2011

It's OLAP time :-)

select month, tran_code, sum(qty)
from tab
group by 1,2
qualify rank() over (partition by trans_code order by month desc) = 1

Dieter

Dieter

Albright 8 posts Joined 07/11
05 Jul 2011

Thaks Dieter. Now to make it more complicated. Here is the full SQL (I made it simpler in my previous post to communicate the concept). This is with you recommended addition.

SELECT
'Base' "Data Source"
,CASE
WHEN a.seas_id = 1 THEN 'Val'
WHEN a.seas_id = 2 THEN 'StPat'
WHEN a.seas_id = 3 THEN 'Estr'
WHEN a.seas_id = 4 THEN 'MDay'
WHEN a.seas_id = 5 THEN 'FDay'
WHEN a.seas_id = 6 THEN 'Grad'
WHEN a.seas_id = 7 THEN 'Hal'
WHEN a.seas_id = 8 THEN 'Thx'
WHEN a.seas_id = 9 THEN 'Cmas'
END "Season"
,a.SEAS_SELL_YR "Sell Year"
,'Retail' "Line Item Type"
,CASE
WHEN a.sbt_typ_cd = '0100' THEN 'BEG. AUDITS'
WHEN a.sbt_typ_cd = '0300' THEN 'REGISTER SALES'
WHEN a.sbt_typ_cd = '0401' THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd = '0402' THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd = '0600' THEN 'ARRIVED SHIPMENTS'
WHEN a.sbt_typ_cd = '0601' THEN 'ARRIVED SHIPMENTS'
WHEN a.sbt_typ_cd = '0701' THEN 'DEDUCTIONS'
WHEN a.sbt_typ_cd = '0702' THEN 'AUDIT RESULTS'
WHEN a.sbt_typ_cd = '0800' THEN 'DEDUCTIONS'
WHEN a.sbt_typ_cd = '0900' THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd = '1000' THEN 'SHRINK'
WHEN a.sbt_typ_cd = '1001' THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd = 'C000' THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd = 'C001' THEN 'INVENTORY ON HAND'
END "Line Items"
,'UPC'||substring(b.rtl_item_cd, 1, 12) "Product Organization"
,'RA'||TRIM(a.acct_nbr) "Customer"
,shrink_proc_dt
,SUM(CASE WHEN a.sbt_typ_cd in ('0402', '0900', '1001')
THEN a.STD_WHSLE_COST_TOT_AMT * -1
ELSE a.STD_WHSLE_COST_TOT_AMT
END) AS "STD WHSLE COST"
,SUM(CASE WHEN a.sbt_typ_cd in ('0402', '0900', '1001')
THEN a.DISC_WHSLE_COST_TOT_AMT * -1
ELSE a.DISC_WHSLE_COST_TOT_AMT
END) AS "DISC WHSLE COST"
,SUM(CASE WHEN a.sbt_typ_cd in ('0402', '0900', '1001')
THEN a.RTL_UNT_PRICE_TOT_AMT * -1
ELSE a.RTL_UNT_PRICE_TOT_AMT
END) AS "RTL UNIT PRICE"
,SUM(CASE WHEN a.sbt_typ_cd in ('0402', '0900', '1001')
THEN a.RTL_UNT_QTY * -1
ELSE a.RTL_UNT_QTY
END) AS "RTL UNIT QTY"
FROM
fint_view.sbt_seas_shrink_dtl_v a
LEFT OUTER JOIN e1dw_view.rtl_item_v b
ON a.rtl_item_id = B.rtl_item_id
WHERE
a.seas_id = 1
AND a.SEAS_SELL_YR = 2011
AND a.sbt_typ_cd IN ('0100', '0300', '0401', '0402', '0600', '0601', '0701', '0702', '0800', '0900', '1000', '1001', 'C000', 'C001')
GROUP BY
1,2,3,4,5,6,7,8
QUALIFY RANK() OVER (PARTITION BY a.sbt_typ_cd ORDER BY shrink_proc_dt DESC) = 1

This FAILS with the message:
3504: Selected non-aggregate values must be part of the associated group.

By removing the case statment and simply selecting 'a.sbt_typ_cd' to match the partition statement it works. But I need to rename these. Example: the a.sbt_typ_cd values of 400, 401, 900, 1001, and C000 are all 'adjustments' and get summed together (some as negatives) into one value. If I don't rename them in the case they do not get summed together. Is there a way to reference the renamed 'a.sbt_typ_cd' in the partition statement?

dnoeth 2698 posts Joined 11/04
05 Jul 2011

The RANK is processed after the GROUP BY, it's similar when you try to user a.sbt_typ_cd in HAVING, the detail data doesn't exist anymore. Just use the alias "Line Items" in the PARTITION clause.

Btw, two remarks

The CASEs could be simplyfied, the first into a "valued case", which is easier to read/maintain:
CASE a.seas_id
WHEN 1 THEN 'Val'
WHEN 2 THEN 'StPat'
WHEN 3 THEN 'Estr'
WHEN 4 THEN 'MDay'
WHEN 5 THEN 'FDay'
WHEN 6 THEN 'Grad'
WHEN 7 THEN 'Hal'
WHEN 8 THEN 'Thx'
WHEN 9 THEN 'Cmas'
END "Season"

the 2nd is a "valued case", too.
Or do a modifed "searched case", which is probably a bit more efficient:
,CASE
WHEN a.sbt_typ_cd = '0100' THEN 'BEG. AUDITS'
WHEN a.sbt_typ_cd = '0300' THEN 'REGISTER SALES'
WHEN a.sbt_typ_cd IN ('0401', '0402', '0900', '1001', 'C000') THEN 'ADJUSTMENTS'
WHEN a.sbt_typ_cd IN ('0600', '0601') THEN 'ARRIVED SHIPMENTS'
WHEN a.sbt_typ_cd IN ('0701', '0800') THEN 'DEDUCTIONS'
WHEN a.sbt_typ_cd = '0702' THEN 'AUDIT RESULTS'
WHEN a.sbt_typ_cd = '1000' THEN 'SHRINK'
WHEN a.sbt_typ_cd = 'C001' THEN 'INVENTORY ON HAND'
END "Line Items"

To get negative values you don't have to multiply a column with -1, it's just "-column"

Dieter

Dieter

Albright 8 posts Joined 07/11
05 Jul 2011

Thanks for the tips on the CASE and neg SUM. Here is my concern. If I put "Line Items" in the PARTITION clause. What will happen here:

sbt_typ_cd shrink_proc_dt value
401 1-Arp-2011 100
401 1-May-2011 120
402 1-Jun-2011 5

The answer I want is 'Adjustments' sum of 115. That because I partition by sbt_typ_cd and take only the most recent 401 and 402, then by making them both alias of 'Adjustments' and summing (402 as a negative) I get my answer. If I partion by 'Adjustments' won't I just the the -5 value by itself, as they are no longer considered different values?

Maybe I need to do a sub-query to eliminate records using the partion and then sum them in the outer query?

dnoeth 2698 posts Joined 11/04
05 Jul 2011

Yep, you're probably right.
If the query without RANK doesn't return the expected (plus the unwanted) rows/data, you have to move it into a Derived Table:
...
FROM
(select ... from fint_view.sbt_seas_shrink_dtl_v a
QUALIFY RANK() OVER (PARTITION BY a.sbt_typ_cd ORDER BY shrink_proc_dt DESC) = 1
) as a
LEFT ...

Dieter

Dieter

Albright 8 posts Joined 07/11
05 Jul 2011

Thanks for all the help. I think that is going to work !!!

You must sign in to leave a comment.