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.