Group Rows with GROUP BY

COUNT and AVG squash all the rows into one row. But often you want one answer per group: per category, per city, per customer. GROUP BY splits the rows into groups, one for each value of a column, and runs the aggregate functions on every group separately. The answer has one row per group.

For example, this prints every city with the number of customers who live there:

var query = "SELECT city, COUNT(*) " +
	"FROM customers " +
	"GROUP BY city "

Select the grouped column too, so each row says which group it is about. Rows with a NULL in that column are not dropped: together they make a group of their own.

Without GROUP BY, the shop's query prints a single row, kitchen | 8 | 18.25: the 8 counts every product, and the category is just one product's. With it, the check prints your query's result, which must be:

category | COUNT(*) | AVG(price)
NULL | 1 | 50
home | 2 | 22
kitchen | 2 | 17
office | 3 | 6
(4 rows)

Your task: the starting code already connects db to shop.db. Below it, declare query as string pieces joined with +, one per clause, and find, for each category of products, how many products it has and their average price.

Assignment
Follow the Coding Tutorial to select the category with its count and average price, take them FROM the products table, then add GROUP BY on the category.

Hint
GROUP BY comes after FROM. Group by the same column you select next to COUNT(*) and AVG(price). Every piece but the last ends with +, and every piece ends with a space inside the quotes.