quotesleft.blogg.se

( 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.