![]() In the above SQL demos, I'm using the COUNT() function however, this same technique should work for most of the MySQL aggregate functions. It's good to have these variations in your back pocket for when you're performing data analysis, merging records, migrating tables, or creating derived tables in MySQL. The COUNT() aggregation function is surprisingly flexible and, it can be used in more ways than you might realize. NOTE: This EXISTS() expression is going to be evaluated for EACH ROW in the Count all of the rows where the 'name' column exists in another table. And now, when we run this SQL in MySQL 5.7, we get the following results (Note that I've removed the SQL comments in order to fit everything into the screen-shot): This is important because MySQL will count any non- NULL value, which includes the "falsy" values 0, FALSE, and ''. ( SELECT 'Nina' AS name, NULL AS isBFF ) UNION ALLĪs you can see, we can use multiple COUNT() variations on the same result-set or GROUP BY cohort! Notice that the last variation - COUNT(expression) - is using OR NULL. ( SELECT 'Lara' AS name, NULL AS isBFF ) UNION ALL ( SELECT 'Lara' AS name, TRUE AS isBFF ) UNION ALL ( SELECT 'Jeff' AS name, NULL AS isBFF ) UNION ALL ( SELECT 'Elle' AS name, NULL AS isBFF ) UNION ALL ( SELECT 'Biff' AS name, TRUE AS isBFF ) UNION ALL ( SELECT 'Anne' AS name, TRUE AS isBFF ) UNION ALL ( SELECT 'Anne' AS name, NULL AS isBFF ) UNION ALL As such, we can exclude rows by returning a NULL value.ĬOUNT( ( name = 'Anne' ) OR NULL ) AS anne_count As with the versions above, only non-NULL expression evaluations will be included any arbitrary expression on each row in the given result-set or GROUP BY cohort. The COUNT( expression ) is the most flexible incarnation, allowing us to evaluate only count any given value ONCE, returning the UNIQUE count in the given result-ĬOUNT( DISTINCT name ) AS unique_name_count, count rows that contain a non-NULL value for the given column. The COUNT( DISTINCT column ) is like the COUNT( column ) in that it will only contains a non-NULL value in the given result-set or GROUP BY cohort. The COUNT( column ) will return the number of rows in which the given column surrounding NULL values - it counts all rows regardless. This version does not incur any special logic The most common form of COUNT() uses the '*' to count all of rows in the given Then, we're going to use all four variations on COUNT() to gather metadata about the "friends" table: ![]() To explore this, I'm going to create a derived table of "friends". This variation is super flexible and you can jam just about anything you want into the "expression". ![]() This variation does not care about the contents of the individual rows, only that they exist.ĬOUNT( column ) - This counts the number of non- NULL values that appear in the given column within the given result-set or GROUP BY cohort.ĬOUNT( DISTINCT column ) - This counts the number of unique, non- NULL values that appear in the given column within the given result-set or GROUP BY cohort.ĬOUNT( expression ) - This evaluates the given expression for each row within the given result-set or GROUP BY cohort and, counts the number of rows in which the expression evaluates to a non- NULL result. ![]() In MySQL 5.x, there are four COUNT() variations (depending on how you look at it):ĬOUNT( * ) - This counts all of the rows in the given result-set or GROUP BY cohort. As such, I thought it would be fun to take a quick look at the COUNT() variations in MySQL 5.7.32. COUNT() - and the other aggregation functions - are surprisingly flexible. As we did this, we were using the COUNT() aggregation function to gather metadata about the records that we were transforming. Yesterday, I was working with fellow InVisioneer, Josh Siok, to transform some MySQL data-tables into a common format. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |