MySQL: GROUP BY Statement

The GROUP BY clause in MySQL is used to group rows in a result set based on one or more columns. It is often used in conjunction with aggregate functions such as COUNT, SUM, AVG, and MAX to generate summary data for groups of rows.

POINTS TO REMEMBER:

The GROUP BY clause is used to group rows in a result set based on one or more columns.
It is often used in conjunction with aggregate functions such as COUNT, SUM, AVG, and MAX to generate summary data for groups of rows.
The columns in the SELECT statement that are not used with an aggregate function must be listed in the GROUP BY clause.
Indexing the columns used in the GROUP BY and WHERE clauses can improve query performance.
When using GROUP BY with multiple columns, the order of the columns in the GROUP BY clause is important, it changes the way the result is grouped.
Avoid using GROUP BY with non-deterministic functions like NOW() or RAND().
When using GROUP BY with a JOIN statement, make sure to include the joined columns in the GROUP BY clause to ensure that the result is grouped correctly.

GROUP BY Statement:

The GROUP BY clause in MySQL is used to group rows in a result set based on one or more columns.

Syntax:

SELECT column_1, aggregate_function(column_2)
FROM table_1
GROUP BY column_1;

Let us count the number of films grouped by rating.

SELECT rating, COUNT(film_id) FROM film
GROUP BY rating;

Output:

Let us get the average rental duration for films grouped by rating.

SELECT rating, AVG(DATEDIFF(return_date, rental_date)) AS avg_duration
FROM film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
GROUP BY rating;

Output:

Let us get the total number of films rented grouped by category.

SELECT category.name, COUNT(DISTINCT inventory.film_id) as films_rented
FROM inventory
JOIN film ON inventory.film_id = film.film_id
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id
GROUP BY category.name;

Output:

'll meet again tomorrow!

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References: w3schools.com/mysql/default.asp

*****All the outputs provided are a snippet of the actual result.

17. MySQL: GROUP BY Statement

POINTS TO REMEMBER:

GROUP BY Statement: