How to Calculate True-False Percentages in PostgreSQL Grouped by Month

In this guide, learn how to calculate the percentages of true and false values from a boolean column in PostgreSQL, grouped by month. Follow our step-by-step guide to achieve accurate results for your data analysis.
---
This video is based on the question https://stackoverflow.com/q/66976363/ asked by the user 'i_will_ask_everything' ( https://stackoverflow.com/u/15568620/ ) and on the answer https://stackoverflow.com/a/66977165/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PostgreSQL - SUM true-false column & group by month giving me a percentage

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Calculating True-False Percentages in PostgreSQL

When working with data in PostgreSQL, there are times when we need to analyze boolean values (true/false) over specific timeframes. A common requirement is to find out what percentage of true and false entries exist for each month within a given date range. This guide will guide you on how to extract this information clearly and effectively by using SQL queries with conditional aggregation.

The Problem Statement

Suppose you have a table containing boolean values encapsulated in a column, alongside a date field. You want to calculate the percentage of true (1) and false (0) values for each month for a specific period. Here’s a brief overview of your starting scenario:

You filtered your data to only keep relevant records.

You want to calculate true and false percentages separately for the months of March and April 2020.

After executing an initial query, you have a set of results like this:

[[See Video to Reveal this Text or Code Snippet]]

From this output, you only care about data from March and April of 2020.

The Solution: SQL Query for Month-wise Percentage

To achieve your goal, you can utilize PostgreSQL’s COUNT function along with conditional filtering. This way, you can group the results by month and calculate the desired percentages. Below, we will break down the solution into clear steps.

Step-by-Step SQL Query

Here is the SQL query you can use to get the percentage of true and false values for March and April:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the SQL Query

COUNT with FILTER:

COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM B) = 3): This counts the number of true values (where column A = 1) in March.

Similarly for April, we count using the same logic but with EXTRACT(MONTH FROM B) = 4.

Percentage Calculation:

We multiply the count of true values by 1.0 to ensure we get a floating-point output.

We then divide this by the total count for that month SUM(COUNT(*)) FILTER (...) to get the percentage.

NULLIF Function:

To avoid division by zero, we use NULLIF to ensure that if there are no records for a given month, we’ll return NULL instead of attempting an erroneous division.

GROUP BY:

Grouping by A allows us to differentiate between the true and false results effectively.

Final Thoughts

Calculating percentages over specific time periods can provide valuable insights into your data trends. By using this SQL approach, you can easily analyze the performance of boolean entries month-over-month in PostgreSQL.

Now that you have the SQL query, you can adapt it to your specific table structure and data needs. Happy querying!

Видео How to Calculate True-False Percentages in PostgreSQL Grouped by Month канала vlogize