Using COUNT(), COUNT(column), And COUNT(expression) Variations To Extract Row Metadata In MySQL 5.7.32

count.sql

SELECT
	-- The most common form of COUNT() uses the '*' to count all of rows in the given
	-- result-set or GROUP BY cohort. This version does not incur any special logic
	-- surrounding NULL values - it counts all rows regardless.
	COUNT( * ) AS total_friend_count,

	-- The COUNT( column ) will return the number of rows in which the given column
	-- contains a non-NULL value in the given result-set or GROUP BY cohort.
	COUNT( isBFF ) AS bff_count,

	-- The COUNT( DISTINCT column ) is like the COUNT( column ) in that it will only
	-- count rows that contain a non-NULL value for the given column. However, it will
	-- only count any given value ONCE, returning the UNIQUE count in the given result-
	-- set or GROUP BY cohort.
	COUNT( DISTINCT name ) AS unique_name_count,

	-- The COUNT( expression ) is the most flexible incarnation, allowing us to evaluate
	-- any arbitrary expression on each row in the given result-set or GROUP BY cohort.
	-- As with the versions above, only non-NULL expression evaluations will be included
	-- in the COUNT(). As such, we can exclude rows by returning a NULL value.
	COUNT( ( name = 'Anne' ) OR NULL ) AS anne_count
FROM
	(

		-- Setup the DERIVED-TABLE for the demo.
		( SELECT 'Anne' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Anne' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Biff' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Elle' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Jeff' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Lara' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Lara' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Nina' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Todd' AS name, NULL AS isBFF )

	) AS friends
;

snippet-1.sql

SELECT
	MIN( value ) AS min_value,
	MAX( value ) AS max_value,
	AVG( value ) AS avg_value,
	AVG( DISTINCT value ) AS distinct_avg_value,
	SUM( value ) AS sum_value,
	SUM( DISTINCT value ) AS distinct_sum_value,
	SUM( IF( value IN ( 1, 2 ), value, NULL ) ) AS sum_expression
FROM
	(

		-- Setup the DERIVED-TABLE for the demo.
		( SELECT 1 AS value ) UNION ALL
		( SELECT NULL AS value ) UNION ALL
		( SELECT 2 AS value ) UNION ALL
		( SELECT 3 AS value ) UNION ALL
		( SELECT 3 AS value ) UNION ALL
		( SELECT 3 AS value ) UNION ALL
		( SELECT NULL AS value ) UNION ALL
		( SELECT 3 AS value ) UNION ALL
		( SELECT 3 AS value )

	) AS numbers
;

sub-query.sql

SELECT
	-- Count all of the rows where the 'name' column exists in another table.
	COUNT(
		-- NOTE: This EXISTS() expression is going to be evaluated for EACH ROW in the
		-- given result-set or GROUP BY cohort.
		EXISTS (

			SELECT
				1
			FROM
				(
					( SELECT 'Anne' AS name ) UNION ALL
					( SELECT 'Lara' AS name )
				) AS innerTable
			WHERE
				innerTable.name = friends.name

		)
		OR NULL
	) AS demo_count
FROM
	(

		-- Setup the DERIVED-TABLE for the demo.
		( SELECT 'Anne' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Anne' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Biff' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Elle' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Jeff' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Lara' AS name, TRUE AS isBFF ) UNION ALL
		( SELECT 'Lara' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Nina' AS name, NULL AS isBFF ) UNION ALL
		( SELECT 'Todd' AS name, NULL AS isBFF )

	) AS friends
;