How to group queries by column

The syntax of the `GROUP BY` and `HAVING` clauses

      SELECT select_list 
      FROM table_source 
      [WHERE search_condition] 
      [GROUP BY group_by_list] 
      [HAVING search_condition] 
      [ORDER BY order_by_list];

Calculate the average list price by category

      SELECT categoryID, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice 
      FROM products 
      GROUP BY categoryID 
      ORDER BY productCount;

group by image

Use columns from multiple tables

      SELECT categoryName, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice 
      FROM products p 
      JOIN categories c ON p.categoryID = c.categoryID 
      GROUP BY categoryName 
      HAVING averageListPrice > 400;

group by image

Use a WHERE clause to filter rows before grouping them

      SELECT categoryName, COUNT(*) AS productCount, AVG(listPrice) AS averageListPrice
      FROM products p 
      JOIN categories c ON p.categoryID = c.categoryID 
      WHERE listPrice > 400 
      GROUP BY categoryName;

group where image

    Description
      The GROUP BY clause groups the rows of a result set based on one or more columns or expressions. It’s typically used in SELECT statements that include aggregate functions.
The HAVING clause specifies a search condition for a group or an aggregate. MySQL applies this condition after it groups the rows that satisfy the condition in the WHERE clause.

    