GROUP BY

• GROUP BY


  It is used to group a set of rows with the same values specified columns into summary rows. Useful for aggregate statistics for each group, such as average, total quantity, max/min values.
• GROUP BY


  1. Often used in conjunction with SUM(), AVG(), MAX(), MIN(), and COUNT()

  2. Often combined with the ORDER BY clause to sort the grouped results, e.g. sort in ASC or DESC by the total quantity in each group
• GROUP BY


  • SELECT category, SUM(stock_qty) AS total FROM library_catalogue GROUP BY category;
  • SELECT category, SUM(stock_qty) AS total FROM library_catalogue GROUP BY category ORDER BY category DESC;
• GROUP BY syntax


  SELECT cName1 , aggregate_function(cName2 ) FROM table_name1 WHERE (Condition) GROUP BY cName1
• GROUP BY with two or more columns


  SELECT cName1 , cName2 , aggregate_function(cName2 ) FROM table_name1 WHERE (Condition) GROUP BY cName1 , cName2
• GROUP BY with two columns


  • SELECT category, LANGUAGE, COUNT(book_title) AS titles FROM library_catalogue GROUP BY category, LANGUAGE;
• Northwind Database: A Province usually consists of many different cities
• How to find the number of companies in each state?

  SELECT COUNT(company), city, state_province FROM customers GROUP BY state_province, city;
• Counting with GROUP BY and ORDER BY


  SELECT COUNT(company), state_province, city FROM customers GROUP BY state_province, city ORDER BY state_province;
• Difference between the two queries


  The first query groups the results by state_province and city, and counts the number of companies in each city within each state_province. The second query just counts the total number of companies.
• GROUP BY with JOIN


  SELECT COUNT(company), ship_name FROM customers INNER JOIN orders ON customers.id = orders.customer_id GROUP BY ship_name;
• Students table has columns: Student_ID, Module_ID, Student_Name, Street_Name, City, State
• Modules table has columns: Module_ID, Title, Assessment_ID, Staff_ID
• GROUP BY with JOIN on Students and Modules tables


  • SELECT COUNT(Title), Title FROM Students INNER JOIN Modules ON Students.Module_ID = Modules.Module_ID GROUP BY Title DESC;
  • SELECT COUNT(Title), Title FROM Students INNER JOIN Modules USING(Module_ID) GROUP BY Title DESC;