Group Rows with GROUP BY in Java

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:

String query = "";
query += "SELECT city, COUNT(*) ";
query += "FROM customers ";
query += "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: for each category of products, find 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, one query += line per clause.

Hint
GROUP BY comes after FROM. Group by the same column you select next to COUNT(*) and AVG(price). End every string with a space, so the pieces join into one query.