For instance, from the data below I can find out the sum, average, minimum and maximum pet food cost overall and by pet type.
Code: Select all
dbadmin=> select * from pets;
pet_type | pet_name | food_cost
------------+------------+-----------
cat | Garfield | 50
guniea pig | Fudge | 25
cat | Oscar Mowl | 100
dog | Fido | 325
dog | Spot | 400
cat | Heathcliff | 75
dog | Sam | 100
rabbit | Oreo | 115
(8 rows)
Code: Select all
dbadmin=> select pet_type,
dbadmin-> pet_name,
dbadmin-> food_cost,
dbadmin-> sum(food_cost) over () sum_overall,
dbadmin-> sum(food_cost) over (partition by pet_type) sum_pet_type,
dbadmin-> avg(food_cost) over () avg_overall,
dbadmin-> avg(food_cost) over (partition by pet_type) avg_pet_type
dbadmin-> from pets
dbadmin-> order
dbadmin-> by pet_type,
dbadmin-> pet_name;
pet_type | pet_name | food_cost | sum_overall | sum_pet_type | avg_overall | avg_pet_type
------------+------------+-----------+-------------+--------------+-------------+--------------
cat | Garfield | 50 | 1190 | 225 | 148.75 | 75
cat | Heathcliff | 75 | 1190 | 225 | 148.75 | 75
cat | Oscar Mowl | 100 | 1190 | 225 | 148.75 | 75
guniea pig | Fudge | 25 | 1190 | 25 | 148.75 | 25
dog | Fido | 325 | 1190 | 825 | 148.75 | 275
dog | Sam | 100 | 1190 | 825 | 148.75 | 275
dog | Spot | 400 | 1190 | 825 | 148.75 | 275
rabbit | Oreo | 115 | 1190 | 115 | 148.75 | 115
(8 rows)
Code: Select all
dbadmin=> select pet_type,
dbadmin-> pet_name,
dbadmin-> food_cost,
dbadmin-> min(food_cost) over () min_overall,
dbadmin-> min(food_cost) over (partition by pet_type) min_pet_type,
dbadmin-> max(food_cost) over () max_overall,
dbadmin-> max(food_cost) over (partition by pet_type) max_pet_type
dbadmin-> from pets
dbadmin-> order
dbadmin-> by pet_type,
dbadmin-> pet_name;
pet_type | pet_name | food_cost | min_overall | min_pet_type | max_overall | max_pet_type
------------+------------+-----------+-------------+--------------+-------------+--------------
cat | Garfield | 50 | 25 | 50 | 400 | 100
cat | Heathcliff | 75 | 25 | 50 | 400 | 100
cat | Oscar Mowl | 100 | 25 | 50 | 400 | 100
guniea pig | Fudge | 25 | 25 | 25 | 400 | 25
dog | Fido | 325 | 25 | 100 | 400 | 400
dog | Sam | 100 | 25 | 100 | 400 | 400
dog | Spot | 400 | 25 | 100 | 400 | 400
rabbit | Oreo | 115 | 25 | 115 | 400 | 115
(8 rows)