Analytics
Enthusiast

## Repeat the aggregation function

Hi there,

I am learning my way in Teradata, so please forgive me for asking a lot of questions. Thanks in advance for those who take time helping me out!

I have a data set with two grouping variable ("Order_No", “Line_No”) and two value variable ("Location”, “Days"):

Order_No   Line_No     Location    Days

A                 1                  Loc1           12

A                 1                  Loc2           10

A                 1                  Loc4           3

A                 2                  Loc1           12

A                 2                  Loc3           5

A                 2                  Loc4           3

A                 3                  Loc1           12

A                 3                  Loc4           3

A                 4                  Loc1           12

A                 4                  Loc4           3

A                 4                  Loc5           5

B                 1                  Loc1           12

B                 1                  Loc2           10

B                 2                  Loc1           12

B                 2                  Loc3           5

C                 1                  Loc4           3

C                 2                  Loc1           12

C                 2                  Loc2           9

C                 3                  Loc1           12

D                 2                  Loc4           3

D                 2                  Loc5           5

With Waldar's help, I was able to identify the single order output, but now I need to extract, from +5000 lines, the location touching every single Line_No with the least average days value PER Order_No.

Below is the desired output:

Order_No   Location    AvgDay

A                 Loc4           3

B                 Loc1           12

C                 n/a              n/a

D                 Loc4           3

How can I repeat the aggregated function for each order?

Thanks!

Accepted Solutions
Highlighted

## Re: Repeat the aggregation function

You need two counts on different levels:

SELECT ord.OrderNum, ordLoc.loc, ordLoc.Days
FROM
( -- number of lines per order
SELECT OrderNum, Count(DISTINCT line) AS Cnt
FROM mvt_datas AS t
GROUP BY OrderNum
) AS ord
LEFT JOIN
( -- number of lines per location/order
SELECT loc, OrderNum, Count(*) AS Cnt, Avg(Days) AS Days
FROM mvt_datas AS t
GROUP BY Loc, OrderNum
) AS ordLoc
ON ordLoc.OrderNum = ord.OrderNum -- same order
AND ordLoc.Cnt = ord.Cnt          -- location covers all lines of an order
QUALIFY Row_Number() Over (PARTITION BY Ord.OrderNum ORDER BY ordLoc.Days DESC) = 1 -- get the location with the lowest average.

Is the base data a table or result of a join?

Windowed Aggregates don't support DISTINCT, otherwise this could be a single access to the base table.

1 ACCEPTED SOLUTION
2 REPLIES 2
Highlighted

## Re: Repeat the aggregation function

You need two counts on different levels:

SELECT ord.OrderNum, ordLoc.loc, ordLoc.Days
FROM
( -- number of lines per order
SELECT OrderNum, Count(DISTINCT line) AS Cnt
FROM mvt_datas AS t
GROUP BY OrderNum
) AS ord
LEFT JOIN
( -- number of lines per location/order
SELECT loc, OrderNum, Count(*) AS Cnt, Avg(Days) AS Days
FROM mvt_datas AS t
GROUP BY Loc, OrderNum
) AS ordLoc
ON ordLoc.OrderNum = ord.OrderNum -- same order
AND ordLoc.Cnt = ord.Cnt          -- location covers all lines of an order
QUALIFY Row_Number() Over (PARTITION BY Ord.OrderNum ORDER BY ordLoc.Days DESC) = 1 -- get the location with the lowest average.

Is the base data a table or result of a join?

Windowed Aggregates don't support DISTINCT, otherwise this could be a single access to the base table.

Enthusiast

## Re: Repeat the aggregation function

Hi Dnoeth,

Thanks for your response. I am so impressed with the prompt and thorough reply from you guys, especially during Thanksgiving weekend!

Your solution worked well, only that I need to change the DESC to ASC to get the least value.

Again, thanks a lot!

Yan