SQL Group by

SQL GROUP BY is a clause to group rows with the same values in one or more columns. It is often used with aggregate functions such as SUM, COUNT, MAX, MIN, and AVG to provide summary information.

Here’s an example of a basic SQL statement using GROUP BY:

SELECT
  column1, SUM(column2)
FROM
  table_name
GROUP BY 
  column1;

In this example, we’re selecting column1 and the sum of column2 from table_name, but grouping the results by the values in column1. As a result, we’ll get a separate sum of column2 for each distinct value of column1.

Here are some key points to remember when working with the SQL GROUP BY clause:

• GROUP BY requires at least one column to group by. You can group by multiple columns, separating them with commas in the GROUP BY clause.
• All columns selected must either be included in the GROUP BY clause or use an aggregate function. Otherwise, you’ll receive an error.
• GROUP BY returns a single row for each group that is created. The aggregate function is applied to each group, and the result is displayed in the output.
• The order of the columns can affect the output order. If you want a specific order, you should include an ORDER BY clause at the end of the statement.
• You can use clauses like WHERE, HAVING, and JOIN with GROUP BY to filter and join the data before grouping it.

For example, let’s say you want to group the rows in the bookshop table by their genre column and calculate the average price for each genre using the AVG function.

SELECT
  genre,
  AVG(price) AS avg_price
FROM
  bookshop
GROUP BY
  genre;

The resulting output will show each genre along with its average price.