Aggregate Functions SELECT sum(column_name) FROM table_name; SELECT avg(column_name) FROM table_name; SELECT max(column_name) FROM table_name; SELECT min(column_name) FROM table_name; SELECT count(column_name) FROM table_name;
Write queries for the following: What's the total revenue for all items?
SELECT sum(revenue) FROM items;
=> intermediate_sql=# SELECT sum(revenue) FROM items;
sum
------
3800
(1 row)What's the average revenue for all items?
SELECT avg(revenue) FROM items;
=> intermediate_sql=# select avg(revenue) from items;
avg
----------------------
950.0000000000000000
(1 row)What's the minimum revenue for all items?
intermediate_sql=# select min(revenue) from items;
min
-----
500
(1 row)What's the maximum revenue for all items?
intermediate_sql=# select max(revenue) from items;
max
------
1200
(1 row)What the count for items with a name?
intermediate_sql=# select count(name) from items;
count
-------
4
(1 row)Typically you count records in a table by counting on the id column, like SELECT COUNT(id) FROM items;. However, it's not necessary for a table to have an id column. What else can you pass to count and still get 5 as your result?
intermediate_sql=# select count(*) from items;
count
-------
5
(1 row)Return all main courses. Hint: What ActiveRecord method would you use to get this?
intermediate_sql=# select * from items where course='main';
id | name | revenue | course
----+----------------+---------+--------
2 | veggie lasagna | 1000 | main
3 | striped bass | 500 | main
(2 rows)Return only the names of the main courses.
intermediate_sql=# select name from items;
name
----------------------
lobster mac n cheese
veggie lasagna
striped bass
arugula salad
(5 rows)Return the min and max value for the main courses.
intermediate_sql=# select max(revenue), min(revenue) from items where course='main';
max | min
------+-----
1000 | 500
(1 row)What's the total revenue for all main courses?
intermediate_sql=# select sum(revenue) from items where course='main';
sum
------
1500
(1 row)intermediate_sql=# select * from items
intermediate_sql-# inner join seasons
intermediate_sql-# on items.seasons_id = seasons.id;
ERROR: column items.seasons_id does not exist
LINE 3: on items.seasons_id = seasons.id;
^
HINT: Perhaps you meant to reference the column "items.season_id".
intermediate_sql=# SELECT * FROM items
intermediate_sql-# INNER JOIN seasons
intermediate_sql-# ON items.season_id = seasons.id;
id | name | revenue | season_id | id | name
----+----------------------+---------+-----------+----+--------
4 | burger | 2000 | 1 | 1 | summer
2 | veggie lasagna | 1000 | 1 | 1 | summer
3 | striped bass | 500 | 1 | 1 | summer
6 | hot dog | 1000 | 1 | 1 | summer
7 | arugula salad | 1100 | 2 | 2 | autumn
1 | lobster mac n cheese | 1200 | 3 | 3 | winter
5 | grilled cheese | 800 | 4 | 4 | spring
(7 rows)Can you get it to display only the name for the item and the name for the season?
intermediate_sql=# SELECT name FROM items as item_name
intermediate_sql-# INNER JOIN seasons
intermediate_sql-# ON items.season_id = seasons.id;
ERROR: invalid reference to FROM-clause entry for table "items"
LINE 3: ON items.season_id = seasons.id;
^
HINT: Perhaps you meant to reference the table alias "item_name".
intermediate_sql=# SELECT name FROM items
intermediate_sql-# INNERJOIN seasons
intermediate_sql-# ON items.season_d = seasons.id;
ERROR: syntax error at or near "seasons"
LINE 2: INNERJOIN seasons
^
intermediate_sql=# SELECT name FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
ERROR: column reference "name" is ambiguous
LINE 1: SELECT name FROM items
^
intermediate_sql=# SELECT (name) FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
ERROR: column reference "name" is ambiguous
LINE 1: SELECT (name) FROM items
^
intermediate_sql=# SELECT name, FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
ERROR: syntax error at or near "FROM"
LINE 1: SELECT name, FROM items
^
intermediate_sql=# SELECT item.name, seasons.name FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
ERROR: missing FROM-clause entry for table "item"
LINE 1: SELECT item.name, seasons.name FROM items
^
intermediate_sql=# SELECT items.name, seasons.name FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
name | name
----------------------+--------
burger | summer
veggie lasagna | summer
striped bass | summer
hot dog | summer
arugula salad | autumn
lobster mac n cheese | winter
grilled cheese | spring
(7 rows)Having two columns with the same name is confusing. Can you customize each heading using AS?
intermediate_sql=# SELECT items.name AS item_name, seasons.name AS season_name FROM items
INNER JOIN seasons
ON items.season_id = seasons.id;
item_name | season_name
----------------------+-------------
burger | summer
veggie lasagna | summer
striped bass | summer
hot dog | summer
arugula salad | autumn
lobster mac n cheese | winter
grilled cheese | spring
(7 rows)Now let's combine multiple INNER JOINs to pull data from three tables items, categories and item_categories.
Write a query that pulls all the category names for arugula salad. Hint: Use multiple INNER JOINs and a WHERE clause.
intermediate_sql=# SELECT items.name FROM items
INNER JOIN item_categories
ON items.id = item_categories.item_id
intermediate_sql-# INNER JOIN categories
intermediate_sql-# ON item_categories.category_id = categories.id
intermediate_sql-# where items.name='arugula salad';
name
---------------
arugula salad
arugula salad
arugula salad
arugula salad
(4 rows)
intermediate_sql=# SELECT items.name categories.name FROM items
INNER JOIN item_categories
ON items.id = item_categories.item_id
INNER JOIN categories
ON item_categories.category_id = categories.id
where items.name='arugula salad';
ERROR: syntax error at or near "."
LINE 1: SELECT items.name categories.name FROM items
^
intermediate_sql=# SELECT items.name, categories.name FROM items
INNER JOIN item_categories
ON items.id = item_categories.item_id
INNER JOIN categories
ON item_categories.category_id = categories.id
where items.name='arugula salad';
name | name
---------------+------------
arugula salad | side
arugula salad | dinner
arugula salad | lunch
arugula salad | vegetarian
(4 rows)Can you change the column headings?
ariqueaguilar=# SELECT items.name AS item_name, categories.name AS category_name FROM items
INNER JOIN item_categories
ON items.id = item_categories.item_id
INNER JOIN categories
ON item_categories.category_id = categories.id
where items.name='arugula salad';
item_name | category_name
---------------+---------------
arugula salad | side
arugula salad | dinner
arugula salad | lunch
arugula salad | vegetarian
(4 rows)LEFT OUTER JOIN
SELECT *
FROM items i
LEFT OUTER JOIN seasons s
ON i.season_id = s.id;
id | name | revenue | season_id | id | name
----+---------------------+---------+-----------+----+--------
6 | hot dog | 1000 | 1 | 1 | summer
2 | veggie lasagna | 1000 | 1 | 1 | summer
3 | striped bass | 500 | 1 | 1 | summer
4 | burger | 2000 | 1 | 1 | summer
7 | arugula salad | 1100 | 2 | 2 | autumn
1 | lobster mac n cheese | 1200 | 3 | 3 | winter
5 | grilled cheese | 800 | 4 | 4 | spring
8 | italian beef | 600 | | |
9 | cole slaw | 150 | | |
10 | ice cream sandwich | 700 | | |
(10 rows)RIGHT OUTER JOIN
ariqueaguilar=# SELECT *
FROM items i
RIGHT OUTER JOIN seasons s
ON i.season_id = s.id;
id | name | revenue | season_id | id | name
----+----------------------+---------+-----------+----+--------
6 | hot dog | 1000 | 1 | 1 | summer
2 | veggie lasagna | 1000 | 1 | 1 | summer
3 | striped bass | 500 | 1 | 1 | summer
4 | burger | 2000 | 1 | 1 | summer
7 | arugula salad | 1100 | 2 | 2 | autumn
1 | lobster mac n cheese | 1200 | 3 | 3 | winter
5 | grilled cheese | 800 | 4 | 4 | spring
(7 rows)ariqueaguilar=# SELECT * FROM items
WHERE revenue > (SELECT revenue FROM items AVG(revenue));
ERROR: column reference "revenue" is ambiguous
LINE 2: WHERE revenue > (SELECT revenue FROM items AVG(revenue));
^
ariqueaguilar=# SELECT * FROM items
WHERE revenue > (SELECT items.revenue FROM items AVG(revenue));
ERROR: more than one row returned by a subquery used as an expression
ariqueaguilar=# SELECT * FROM items
WHERE revenue > (SELECT AVG(revenue) FROM items);
id | name | revenue | season_id
----+----------------------+---------+-----------
1 | lobster mac n cheese | 1200 | 3
2 | veggie lasagna | 1000 | 1
4 | burger | 2000 | 1
6 | hot dog | 1000 | 1
7 | arugula salad | 1100 | 2
(5 rows)