CPSC340A - Day 8 Notes

Review Aggregate Functions

Aaggregate function computes value for entire column
```
SELECT AVG(number) FROM TableName;
```

Unless it is paired with a filter

SELECT AVG(number) FROM TableName WHERE text = 'Something';

If you want to get the average of a different group of rows, need another query
```
SELECT AVG(number) FROM TableName WHERE text = 'Something else';
```

Group By

To perform aggregate functions on groups of rows without multiple queries, use GROUP BY
```
SELECT text, AVG(number) FROM TableName GROUP BY text;
```

Can group by more than one column

SELECT text, date, AVG(number) FROM TableName GROUP BY text, date;

All columns with the same two columns are grouped
Just like with non-group aggregate functions, non-aggregate columns can not be included
```
SELECT id, text, AVG(number) FROM TableName GROUP BY text; -- Error
```

Can not use derived columns in group by

SELECT CONCAT(text1, text2) AS text, AVG(number) FROM TableName GROUP BY text; -- Error
SELECT CONCAT(text1, text2), AVG(number) FROM TableName GROUP BY CONCAT(text1, text2); -- Error in some implementation of SQL

If you do want to group by a derived column, need to use a subquery

SELECT text, AVG(number)  FROM (SELECT CONCAT(text1, text2) AS text, number FROM TableName) AS Concatenated GROUP BY text;

Or if possible just use the two separtate columns in the group by

SELECT text1, text2, AVG(number)  FROM TableName GROUP BY text1, text2;

Groups can be used with joins

SELECT text, AVG(number) FROM Table1 INNER JOIN Table2 GROUP BY text;

With filters

SELECT text, AVG(number) FROM Table1 WHERE number > 0 GROUP BY text;

With ordering

SELECT text, AVG(number) FROM Table1 GROUP BY text ORDER BY text DESC;
SELECT text, AVG(number) FROM Table1 GROUP BY text ORDER BY AVG(number);

Inside of the filter clause

First an example of an agrregate function in the WHERE clause

SELECT * FROM TableName WHERE number > (SELECT AVG(number) FROM TableName);

Now add GROUP BY

SELECT * FROM TableName WHERE number > (SELECT AVG(number) FROM TableName GROUP BY text); -- Error

But this is comparing a single number to a table of numbers
Which produces an error

To prevent, use greater than all operator

SELECT * FROM TableName WHERE number > ALL (SELECT AVG(number) FROM TableName GROUP BY text);

There is also a greater than any comparison

SELECT * FROM TableName WHERE number > ANY (SELECT AVG(number) FROM TableName GROUP BY text);

Can also filter the grouped aggregate rows

SELECT text, AVG(number) FROM TableName GROUP BY text WHERE AVG(number) > 3; -- Error

This doesn’t work because the WHERE clause of a select statement goes before the GROUP BY and is performed before the grouping

To filter the grouped rows use HAVING keyword

SELECT text, AVG(number) FROM TableName GROUP BY text HAVING AVG(number) > 3;