Thread by @orc_arn

Chapter 8: Grouping and Aggregate functions of #LearningSQL @OReillyMedia

This thread will be a brief summary and a short one.
Let's dive in

1. GROUP BY is one of the most fundamental functions in #MySQL or any kind. It helps us to retrieve counts, distinct values and etc by certain categorical data.
An example:

2. If you need to filter your tables while using GROUP BY, generally you cannot use WHERE clause, instead use HAVING.

In this example we filtered the data after creating groups. If we'd have used WHERE before GROUP BY, server will raise an error.

2a. The reason for that the groups haven't been created yet and where clause will be evaluated before creating the groups. Therefore we need to filter after creating groups and use HAVING.

3. Aggregate Functions

max, min, avg, sum, count .... For more visit dev.mysql.com/doc/refman/8.0…

4. You can use COUNT( DISTINCT customer_id) to get distinct counts of the values

-- Remember, you can always group by with multiple columns, expressions

5. WITH ROLLUP
returns both distinct counts and total counts of each group and total counts. Easier than UNION two columns and faster.

Returns:

actor_id NULL is all the data
actor_id 1 and rating NULL is all movies that actor 1 has taken a role in.

🔚 I will continue to read and share my notes here, if you would like to see more, consider following @orc_arn

See you on the following thread.

Have a nice day