Is it possible to specify condition in Count()?

Advanced conditional counting with CASE

In SQL, the COUNT() function can be leveraged within a CASE statement for applying conditions without a WHERE clause. This can keep your dataset intact during aggregation:

SELECT 
  COUNT(CASE WHEN condition THEN 1 END) AS ConditionalCount
FROM 
  your_table;


This way the COUNT() only includes rows that meet the given condition.

Counting categories with CASE

Categories nested in the same column can be counted separetely using CASE within COUNT():

SELECT 
  COUNT(CASE WHEN category = 'A' THEN 1 END) AS CategoryACount,
  COUNT(CASE WHEN category = 'B' THEN 1 END) AS CategoryBCount
FROM 
  your_table;


Fair warning — this might start a category rivalry!

The CLEAN approach with FILTER and COUNTIF

FILTER clause and the COUNTIF function, which are widely used in PostgreSQL and SQLite, dispense conditioned aggregations with aplomb:

SELECT 
  COUNT(*) FILTER (WHERE condition) AS ConditionalCount
FROM 
  your_table;


Why use a sieve, when you have a FILTER!

In Google BigQuery, COUNTIF plays dexterously with complex conditions:

SELECT 
  COUNTIF(condition) AS ConditionalCount
FROM 
  your_table;


COUNTIF — The Swiss Army knife for conditional counts.

For more on the FILTER clause's compatibility across various platforms, visit modern-sql.com/feature/filter.

Binary logic Simplified

Binary logic (1 for true, 0 for false) simplifies conditional counts:

SELECT 
  SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS ConditionalCount
FROM 
  your_table;


This method punches above its weight when dealing with multiple conditions in one execution.

Robust counting with IIF

In SQL Server, the IIF function brings a more concise alternative to the CASE:

SELECT 
  COUNT(IIF(condition, 1, NULL)) AS ConditionalCount
FROM 
  your_table;


Like a friendly conditional sorting hat, IIF excludes NULLs automatically, ensuring accurate counts.