How to Display 0 Values in PostgreSQL with a WHERE Clause

Описание: Learn how to modify your PostgreSQL queries to include items with zero orders, ensuring you get a complete picture of your data.
---
This video is based on the question https://stackoverflow.com/q/68863188/ asked by the user 'Pierrem' ( https://stackoverflow.com/u/15716622/ ) and on the answer https://stackoverflow.com/a/68863205/ 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 - how to have the 0 values using a WHERE?

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

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Display 0 Values in PostgreSQL with a WHERE Clause

When working with databases, especially when it comes to reporting and analytics, it's common to encounter situations where you need to include items that have not been sold or have no transactions associated with them. In PostgreSQL, this can be a little tricky because your standard joins might not return rows for items without orders. This guide will address how to correctly structure your SQL queries to display items with a 0 value for orders, alongside items that have actual order counts.

The Problem

Let's set the context. Imagine you have two tables - Item and Order. You want to report the number of orders for each item over a specific time period, but you still want to display items that had no orders during that time.

Initially, you might have written a query like this:

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

This query will only return items that had orders, which means you'll miss out on those items with a count of 0. For example, if Chocolate, Cake, and Muffin had orders but Pancake and Donut did not, your result would look like this:

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

However, what you want is to have every item listed along with their respective order counts, even if that count is 0:

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

The Solution

Step 1: Use LEFT JOIN Instead of INNER JOIN

To solve this, you will want to switch from an INNER JOIN to a LEFT JOIN. The LEFT JOIN will include all rows from the left table (Item) and only matching rows from the right table (Order). If there is no match, the result will contain NULL values for the columns from the right table.

Step 2: Move Conditions into the ON Clause

Next, you should also consider moving the date filtering condition to the ON clause of your join. This will ensure that the left join works properly while applying the date condition.

Final Query

Here’s how your modified SQL query should look:

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

Explanation of the Query

Here's what each part of the query does:

SELECT i.name, COUNT(o.id) AS order_count: This selects the name of the item from the Item table and counts the number of associated orders. If there are no associated orders, this will return 0.

FROM Item i: This indicates the Item table is the starting point for the query.

LEFT JOIN Order o ON i.id = o.it_id AND o.date >= '2021-01-08': This joins the Order table on the item ID while also applying the date filter. Thanks to the LEFT JOIN, all items will be included even if they have no corresponding entries in the Order table.

GROUP BY i.name: This groups the result by item name so that counts can be aggregated.

Conclusion

By following the above steps, you can effectively structure your PostgreSQL queries to display all items, including those with a 0 order count. This is crucial for thorough reporting, allowing for a complete overview of your data.

If you have further questions or specific scenarios you would like to discuss, feel free to leave a comment! Happy querying!