Скачать или смотреть Mastering SQL to Sum Records: Show All Purchases While Filtering by Date

Discover how to sum all purchases in `SQL` regardless of the date filter applied to the final output. Learn to use window functions effectively!
---
This video is based on the question https://stackoverflow.com/q/64014059/ asked by the user 'SQL_newbie' ( https://stackoverflow.com/u/14322388/ ) and on the answer https://stackoverflow.com/a/64014138/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) 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: SQL sum records that wont appear in the final result

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.
---
Mastering SQL to Sum Records: Show All Purchases While Filtering by Date

If you're new to SQL, you may find yourself grappling with how to filter records and summarize data simultaneously. Today, we're covering a common challenge in SQL: how to sum all transactions regardless of a date filter applied to the final output.

The Problem at Hand

Imagine you have a table of purchases from different users, and you want to only show recent purchases while still providing the total spending for that user. For instance, if we take the following table titled Table1:

IDNamePurchase_DatePurchase_Value1John Doe01/01/2020101John Doe30/01/2020251John Doe20/02/2020202Santa Claus01/01/202015Desired Output

You want to select records from this table where the purchase date is after 19/02/2020 while displaying the total sum of purchases for each person. The expected output should look something like this:

IDNamePurchase_DatePurchase_ValueTotal_Purchase_Value1John Doe20/02/20202055In this example, Santa Claus does not appear because he has no purchases in the desired date range. However, John Doe's total amount spent is reflected correctly, showing the sum of all his purchases.

Solution: Using SQL Window Functions

To achieve this, we can use SQL window functions, specifically the SUM() function with partitioning. Let’s break down the SQL query that accomplishes this.

The SQL Query

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

Breakdown of the Query

Subquery:

The inner query selects all columns from table1.

Using SUM(purchase_value) OVER(PARTITION BY id), we compute the total purchase value for each unique user identified by id. This computation is done over all records, irrespective of the date filter.

Outer Query Filter:

The outer query then filters this data based on the relevant condition, which is selecting purchases that happen after 19/02/2020.

Final Note on Database Syntax

It's worth mentioning that SQL syntax can vary between different database systems. The above DATE literal syntax is fairly standard, but if you are using a specific database (like MySQL, PostgreSQL, etc.), you may need to adjust the date formatting according to its requirements.

Conclusion

By employing the SUM() window function in your SQL query, you can effortlessly filter records based on specific criteria while still aggregating data for a comprehensive summary. This method not only simplifies complex queries but also enhances the interpretability of your data.

With this understanding, you can now tackle similar problems in the future with confidence. Happy querying!