Is there a way to incorporate aggregate values and non-aggregate in one query without using subqueries?

Question

In Teradata what I want to do is create two aggregate values from doing a group by keep the aggregate values and compute a new value from the aggregate value.

I have done this simple query

select 
  a.*, 
  def / n as derived_dr 
from
  (select 
    obsdate
    , sum(def) as def
    , count(*) as n         
from 
    some_table
group by obsdate) as a;

But in SAS/SQL I can do for example

    select 
      a.*
      , sum(def) as def
      , count(*) as n 
      , def / n as derived_dr 
     from
         some_table as a;

which a lot simpler and easier to understand. But if I try the above code in Teradata it gives an error

selected non-aggregate values must be part of the associated group

Is my original Teradata solution the best way to achieve this? I am looking for the most proper way to do this in Teradata, I think there should be a solution without using a sub-query.

Answer 1

Mixing both details and aggregates in the same level are a task for Windowed Aggregate Functions in Teradata/Standard SQL:

sum(def) over () -- global sum
sum(def) over (partition by obsdate) -- group sum, similar to GROUP BY