bringing in values associated with aggregate functions

Database
Enthusiast

bringing in values associated with aggregate functions

I have a table structure that is something like this:

Table_A:

ID1, ID2, Value

a,1,5

a,2,6

 

Table_B

ID2, Date

1, 2017/06/01

2, 2017/01/01

 

Ideally, I'd like to pull ID1, Min(ID2), Max(ID2), MIN_VALUE, MAX_VALUE, MIN_DATE, MAX_DATE

A, 1, 2, 5, 6, 2017/06/01, 2017/01/01

 

The only thing I can think of (spoiler alert: it is completely inefficient) is to:

select

a.id1

,b.id2 as MIN_ID2

,c.id2 as MAX_ID2

,b.value as MIN_ID2_VALUE

,c.value as MAX_ID2_VALUE

,d.Date as MIN_ID2_DATE

,e.Date as MAX_ID2_DATE

from (select ID1, min(ID2) as MIN_ID2, max(ID2) as MAX_ID2 from TABLE_A group by ID1) a

left outer join (select ID1, ID2, Value from TABLE_A) b on a.min_id2 = b.id2

left outer join (select ID1, ID2, Value from TABLE_A) c on a.max_id2 = c.id2

left outer join Table_B d on b.ID2 = d.ID2

left outer join Table_B e on c.id2 = e.id2

 

However, I think the question can be broken down into a simpler one. Given the table structure

ID1, ID2, Value

a,1,5

a,2,6

a,3,7

 

How would I be able to pull the following: ID1, MAX(ID2), MAX_ID2_VALUE.  Select ID1, Max(ID2), Value requires grouping on the value, which won't get me what I need. I'm hoping there's an easier way than needing to use extra joins.

 

 

  • aggregate
  • grouping
  • Teradata
  • values
1 REPLY
Teradata Employee

Re: bringing in values associated with aggregate functions

It's not clear that you actually need to select every Value.  If not, then this would work:

 

Select A.ID1, Min(B.ID2), Max(B.ID2), MIN(A.VALUE), MAX(A.VALUE), MIN(B.DATE), MAX(B.DATE)

from Table_A A
join Table_B B on A.ID1 = B.ID1

Group By 1

 

However, if you do need every value then put the above in a derived table, something like:

Select AA.ID1, AA.Value, S.Min_ID2, S.Max_ID2, S.MIN_VALUE, S.MAX_VALUE, S.MIN_DATE, S.MAX_DATE
From Table_A AA
Join (Select A.ID1, Min(B.ID2), Max(B.ID2), MIN(A.VALUE), MAX(A.VALUE), MIN(B.DATE), MAX(B.DATE)
    from Table_A A
    join Table_B B on A.ID1 = B.ID1
    Group By 1

    ) S (ID1, Min_ID2, Max_ID2, MIN_VALUE, MAX_VALUE, MIN_DATE, MAX_DATE)
ON AA.ID1 = S.ID1