Filtering on two separate colums

by Hari Krishnan   Last Updated October 09, 2019 21:06 PM - source

The schema

Consider a table with columns

  1. id uuid
  2. type char
  3. is_active Boolean

Here are some facts about the data in this table

  1. type is either 'A' or 'B'
  2. There will be a maximum of 2 records for any id, one for each type
  3. A record with type 'A' will exist for all IDs. A record with type 'B' is optional. That is to say, there will be a minimum of one record for any id, with type 'A'

The scenario

A user can select one or both the types to apply a filter. For example, a user might select type 'A' and filter based on is_active true. Or they might select type 'A' with is_active true AND type 'B' with is_active false.

Our task is to count the number of records which satisfies the is_active condition for selected types. Here are some scenarios better explanation

  1. If user selects type 'A' and is_active true, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B', regardless of is_active)
  2. If user selects type 'A' with is_active true and type 'B' with is_active false, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B' and is_active is false)

If user restricts is_active for type 'B', IDs without type 'B' should not be included.

If user restricts is_active for type 'A', IDs without type 'B' should be included.

The question

Can this be achieved without using stored procedures? Any help will be greatly appreciated.

Tags : query count


Related Questions


How to make multiple counts in one query?

Updated March 10, 2017 23:06 PM

Counting a column of numbers

Updated April 28, 2019 22:06 PM

MYSQL sub-query COUNT

Updated October 23, 2018 15:06 PM

How to get the count percentage in 100% of a query?

Updated November 20, 2018 17:06 PM