CODEWITHSUNDEEP
sqldatabasehivebigdata
Alex Zamora
Alex Zamora

Reputation: 11

Generating MIN, AVG, MAX columns from a column. [SQL] Hive

I have a database with a column for oil prices. My goal is to generate a table with columns showing Min Price, AvgPrice, and Max Price based off of the Oil value column grouped by location. Where oil.frequency = 'A'

this is my current query 

select location, 
       min(oil.value) over (partition BY oil.location), 
       max(oil.value) over (partition BY oil.location), 
       avg(oil.value) over (partition BY oil.location)
FROM OIL 
Where oil.frequency = 'A'
GROUP BY oil.location, oil.value;

The images below show my data table and the 2nd image shows how I want the data to appear.

enter image description here

enter image description here

Upvotes: 0

Views: 518

Answers (1)

forpas
forpas

Reputation: 164089

You don't need window functions.
Just group by location and aggregate: 

select location, 
       min(value), 
       max(value), 
       avg(value)
FROM OIL 
Where frequency = 'A'
GROUP BY location;

Upvotes: 2

Related Questions