Chapter 8: Grouping and Aggregate functions of #LearningSQL @OReillyMedia
This thread will be a brief summary and a short one.
Let's dive in
This thread will be a brief summary and a short one.
Let's dive in
https://twitter.com/orc_arn/status/1530894610817499136
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:
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.
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.
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
-- 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 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.
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
See you on the following thread.
Have a nice day
• • •
Missing some Tweet in this thread? You can try to
force a refresh
