CODEWITHSUNDEEP
sqlgroup-by
x89
x89

Reputation: 3460

exclude one column from grouping in sql query

I run an sql query to calculate mean and count after grouping combinations of themes, and country. This works fine.

create table georisk as (select themes, country , AVG(value) as mean, count(themes) as count from mytable group by themes, country order by themes, suppliers_country)

However, now I want to add an additional col to my table with the value max(date_t)without grouping by anything. A single value will be added for all the rows. If I do this:

create table georisk as (select themes, country , AVG(value) as mean, count(themes) as count, max(date_t) as last_included_date from mytable group by themes, country order by themes, suppliers_country)

the max(date_t) will also be according to the grouping. How can I just extract one max value within a single query?

Upvotes: 0

Views: 1177

Answers (1)

MatBailie
MatBailie

Reputation: 86716

I think you want this...

select
  themes,
  country,
  AVG(value) as mean,
  count(themes) as count,
  max(date_t) as last_included_date,
  max(max(date_t)) over ()  as very_last_include_date
from
  mytable
group by
  themes,
  country
order by
  themes,
  country         -- Note, you had a typo here ; suppliers_country

The GROUP BY is evaluated before the SELECT, then the aggregates are evaluated, then the window function is evaluated.

MAX(
  MAX(date_t)  -- normal aggregate
)
OVER ()        -- window function across whole result set's values of `MAX(date_t)`

Normally a window function has a PARTITION BY, leaving it empty means 'no partition' and therefor 'whole result set'.

Upvotes: 2

Related Questions