Analytics
Highlighted
Enthusiast

## how to get min of an average

Hi there,

I am new to the Teradata world, and need some help to get the following resolved:

I need to find the location that touches all order lines with the least days. The desired result for the following should be A04, which showed up for every single line (i.e. count (distinct Line) =5), with the least average days of of 2 (instead of A05 that has 3).

How do we do min of an average?

Thanks a lot!

OrderNum/Line/Qty/Value/Loc/Days

 11111 10 1 23 A04 2 11111 10 1 25 A05 3 11111 20 3 273 A04 2 11111 20 3 176 A05 3 11111 20 3 78 A06 3 11111 30 1 17 A04 2 11111 30 1 4 A05 3 11111 30 1 2 A07 1 11111 40 4 15 A04 2 11111 40 4 6 A05 3 11111 40 4 10 AT0 999 11111 40 4 6 A07 1 11111 50 24 777 A04 2 11111 50 24 181 A05 3 11111 50 24 4 ASJ 999 11111 50 24 33 A07 1 11111 50 24 2 AW0 999

Accepted Solutions

## Re: how to get min of an average

Hi Yan,

There are some informations missing to provide a full answer but this should set you on the right track.

```create multiset volatile table mvt_datas
( OrderNum  integer
, Line      integer
, Qty       integer
, Val       integer
, Loc       char(3)
, Days      integer
)
primary index (OrderNum)
on commit preserve rows;

insert into mvt_datas values (11111, 10,  1,  23,  'A04',   2);
insert into mvt_datas values (11111, 10,  1,  25,  'A05',   3);
insert into mvt_datas values (11111, 20,  3, 273,  'A04',   2);
insert into mvt_datas values (11111, 20,  3, 176,  'A05',   3);
insert into mvt_datas values (11111, 20,  3,  78,  'A06',   3);
insert into mvt_datas values (11111, 30,  1,  17,  'A04',   2);
insert into mvt_datas values (11111, 30,  1,   4,  'A05',   3);
insert into mvt_datas values (11111, 30,  1,   2,  'A07',   1);
insert into mvt_datas values (11111, 40,  4,  15,  'A04',   2);
insert into mvt_datas values (11111, 40,  4,   6,  'A05',   3);
insert into mvt_datas values (11111, 40,  4,  10,  'AT0', 999);
insert into mvt_datas values (11111, 40,  4,   6,  'A07',   1);
insert into mvt_datas values (11111, 50, 24, 777,  'A04',   2);
insert into mvt_datas values (11111, 50, 24, 181,  'A05',   3);
insert into mvt_datas values (11111, 50, 24,   4,  'ASJ', 999);
insert into mvt_datas values (11111, 50, 24,  33,  'A07',  1 );
insert into mvt_datas values (11111, 50, 24,   2,  'AW0', 999);

collect statistics column (OrderNum) on mvt_datas;```

Note it's easier for anyone who's willing to help to provide your data in a script like this one.

I see two methods :

```  select Loc
from mvt_datas
where OrderNum = 11111
group by Loc
having count(*) = (select count(distinct Line) from mvt_datas where OrderNum = 11111)
qualify row_number() over(order by avg(Days) asc) = 1;```

The having part is to get only the location that shows up for every line.

It assumes (OrderNum, Line, Loc) is unique. If not, modify the count(*) by count(distinct Line).

The qualify is for getting the min Days part. I used avg here, but min / max / sum would have worked because it seems the Days column is only related to Loc column.

The other one may be easier to understand but gives the Location the more present (not necessarely on every Line) with the least days :

```  select top 1 Loc
from mvt_datas
where OrderNum = 11111
group by Loc
order by count(*) desc
, avg(Days) asc;```
1 ACCEPTED SOLUTION
2 REPLIES 2

## Re: how to get min of an average

Hi Yan,

There are some informations missing to provide a full answer but this should set you on the right track.

```create multiset volatile table mvt_datas
( OrderNum  integer
, Line      integer
, Qty       integer
, Val       integer
, Loc       char(3)
, Days      integer
)
primary index (OrderNum)
on commit preserve rows;

insert into mvt_datas values (11111, 10,  1,  23,  'A04',   2);
insert into mvt_datas values (11111, 10,  1,  25,  'A05',   3);
insert into mvt_datas values (11111, 20,  3, 273,  'A04',   2);
insert into mvt_datas values (11111, 20,  3, 176,  'A05',   3);
insert into mvt_datas values (11111, 20,  3,  78,  'A06',   3);
insert into mvt_datas values (11111, 30,  1,  17,  'A04',   2);
insert into mvt_datas values (11111, 30,  1,   4,  'A05',   3);
insert into mvt_datas values (11111, 30,  1,   2,  'A07',   1);
insert into mvt_datas values (11111, 40,  4,  15,  'A04',   2);
insert into mvt_datas values (11111, 40,  4,   6,  'A05',   3);
insert into mvt_datas values (11111, 40,  4,  10,  'AT0', 999);
insert into mvt_datas values (11111, 40,  4,   6,  'A07',   1);
insert into mvt_datas values (11111, 50, 24, 777,  'A04',   2);
insert into mvt_datas values (11111, 50, 24, 181,  'A05',   3);
insert into mvt_datas values (11111, 50, 24,   4,  'ASJ', 999);
insert into mvt_datas values (11111, 50, 24,  33,  'A07',  1 );
insert into mvt_datas values (11111, 50, 24,   2,  'AW0', 999);

collect statistics column (OrderNum) on mvt_datas;```

Note it's easier for anyone who's willing to help to provide your data in a script like this one.

I see two methods :

```  select Loc
from mvt_datas
where OrderNum = 11111
group by Loc
having count(*) = (select count(distinct Line) from mvt_datas where OrderNum = 11111)
qualify row_number() over(order by avg(Days) asc) = 1;```

The having part is to get only the location that shows up for every line.

It assumes (OrderNum, Line, Loc) is unique. If not, modify the count(*) by count(distinct Line).

The qualify is for getting the min Days part. I used avg here, but min / max / sum would have worked because it seems the Days column is only related to Loc column.

The other one may be easier to understand but gives the Location the more present (not necessarely on every Line) with the least days :

```  select top 1 Loc
from mvt_datas
where OrderNum = 11111
group by Loc
order by count(*) desc
, avg(Days) asc;```
Enthusiast

## Re: how to get min of an average

Hi Waldar,

Thanks a lot for the detailed explanation!!! Option 1 was exactly what I was looking for! All notes taken!

Yan