{"id":4119,"date":"2024-09-23T11:23:35","date_gmt":"2024-09-23T03:23:35","guid":{"rendered":"https:\/\/five.co\/?p=4119"},"modified":"2026-05-04T16:42:26","modified_gmt":"2026-05-04T08:42:26","slug":"sql-cumulative-sum","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-cumulative-sum\/","title":{"rendered":"SQL Cumulative Sum: A Practical Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL cumulative sum is a crucial technique for data analysis and reporting. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It allows you to calculate running totals, track progress over time, and uncover trends in your data. In this article, we&#8217;ll dive deep into the world of cumulative sums in SQL, exploring various methods and real-world applications.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-wz51n wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-wz51n \"><div class=\"eb-toc-container eb-toc-wz51n  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;What is a Cumulative Sum?&quot;,&quot;text&quot;:&quot;What is a Cumulative Sum?&quot;,&quot;link&quot;:&quot;what-is-a-cumulative-sum&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Window Functions: The Key to Cumulative Sums&quot;,&quot;text&quot;:&quot;Window Functions: The Key to Cumulative Sums&quot;,&quot;link&quot;:&quot;window-functions-the-key-to-cumulative-sums&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Partitioning: Cumulative Sums Within Groups&quot;,&quot;text&quot;:&quot;Partitioning: Cumulative Sums Within Groups&quot;,&quot;link&quot;:&quot;partitioning-cumulative-sums-within-groups&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;text&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;link&quot;:&quot;real-world-use-case-sales-performance-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Techniques: Moving Averages and Percent of Total&quot;,&quot;text&quot;:&quot;Advanced Techniques: Moving Averages and Percent of Total&quot;,&quot;link&quot;:&quot;advanced-techniques-moving-averages-and-percent-of-total&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Moving Average&quot;,&quot;text&quot;:&quot;Moving Average&quot;,&quot;link&quot;:&quot;moving-average&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Percent of Total&quot;,&quot;text&quot;:&quot;Percent of Total&quot;,&quot;link&quot;:&quot;percent-of-total&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Performance Considerations&quot;,&quot;text&quot;:&quot;Performance Considerations&quot;,&quot;link&quot;:&quot;performance-considerations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Wrapping Up&quot;,&quot;text&quot;:&quot;Wrapping Up&quot;,&quot;link&quot;:&quot;wrapping-up&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;What is a Cumulative Sum?&quot;,&quot;value&quot;:&quot;what-is-a-cumulative-sum&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Window Functions: The Key to Cumulative Sums&quot;,&quot;value&quot;:&quot;window-functions-the-key-to-cumulative-sums&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Partitioning: Cumulative Sums Within Groups&quot;,&quot;value&quot;:&quot;partitioning-cumulative-sums-within-groups&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Use Case: Sales Performance Analysis&quot;,&quot;value&quot;:&quot;real-world-use-case-sales-performance-analysis&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Advanced Techniques: Moving Averages and Percent of Total&quot;,&quot;value&quot;:&quot;advanced-techniques-moving-averages-and-percent-of-total&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Moving Average&quot;,&quot;value&quot;:&quot;moving-average&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Percent of Total&quot;,&quot;value&quot;:&quot;percent-of-total&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Wrapping Up&quot;,&quot;value&quot;:&quot;wrapping-up&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#what-is-a-cumulative-sum\">What is a Cumulative Sum?<\/a><li><a href=\"#window-functions-the-key-to-cumulative-sums\">Window Functions: The Key to Cumulative Sums<\/a><li><a href=\"#partitioning-cumulative-sums-within-groups\">Partitioning: Cumulative Sums Within Groups<\/a><li><a href=\"#real-world-use-case-sales-performance-analysis\">Real-World Use Case: Sales Performance Analysis<\/a><li><a href=\"#advanced-techniques-moving-averages-and-percent-of-total\">Advanced Techniques: Moving Averages and Percent of Total<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#wrapping-up\">Wrapping Up<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">What is a Cumulative Sum?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A cumulative sum, also known as a running total, is the sum of a sequence of numbers which is updated each time a new number is added to the sequence. In SQL, this translates to calculating the sum of a column&#8217;s values up to the current row.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s jump right into an example to illustrate this concept:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    order_date,\n    order_amount,\n    SUM(order_amount) OVER (ORDER BY order_date) AS cumulative_sum\nFROM \n    orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_amount) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> order_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cumulative_sum<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates the cumulative sum of order amounts, ordered by date. The result might look like this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>order_date  | order_amount | cumulative_sum\n------------|--------------|-----------------\n2023-01-01  | 100          | 100\n2023-01-02  | 150          | 250\n2023-01-03  | 200          | 450\n2023-01-04  | 75           | 525<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">order_date  | order_amount | cumulative_sum<\/span><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">------------|--------------|-----------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">  | <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">02<\/span><span style=\"color: #ABB2BF\">  | <\/span><span style=\"color: #D19A66\">150<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">250<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">03<\/span><span style=\"color: #ABB2BF\">  | <\/span><span style=\"color: #D19A66\">200<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">450<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">04<\/span><span style=\"color: #ABB2BF\">  | <\/span><span style=\"color: #D19A66\">75<\/span><span style=\"color: #ABB2BF\">           | <\/span><span style=\"color: #D19A66\">525<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Window Functions: The Key to Cumulative Sums<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>SUM() OVER ()<\/code> syntax we used above is called a window function. Window functions are the secret sauce that makes cumulative sums in SQL possible. They allow us to perform calculations across a set of rows that are related to the current row.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a breakdown of the window function we used:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>SUM()<\/code>: This is the aggregate function we&#8217;re using.<\/li>\n\n\n\n<li><code>OVER()<\/code>: This clause defines the window of rows our function will operate on.<\/li>\n\n\n\n<li><code>ORDER BY order_date<\/code>: This specifies how we want to order our rows for the cumulative sum calculation.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Partitioning: Cumulative Sums Within Groups<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes, you need to calculate cumulative sums within specific groups. This is where the <code>PARTITION BY<\/code> clause comes in handy. Let&#8217;s say we want to calculate cumulative sums for each product category:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    order_date,\n    product_category,\n    order_amount,\n    SUM(order_amount) OVER (\n        PARTITION BY product_category \n        ORDER BY order_date\n    ) AS category_cumulative_sum\nFROM \n    orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_amount) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> product_category <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> order_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> category_cumulative_sum<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will give us a running total for each product category independently. The result might look like this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>order_date  | product_category | order_amount | category_cumulative_sum\n------------|------------------|--------------|-------------------------\n2023-01-01  | Electronics      | 100          | 100\n2023-01-02  | Electronics      | 150          | 250\n2023-01-01  | Furniture        | 200          | 200\n2023-01-03  | Electronics      | 75           | 325\n2023-01-02  | Furniture        | 300          | 500<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">order_date  | product_category | order_amount | category_cumulative_sum<\/span><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">------------|------------------|--------------|-------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">  | Electronics      | <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">02<\/span><span style=\"color: #ABB2BF\">  | Electronics      | <\/span><span style=\"color: #D19A66\">150<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">250<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">  | Furniture        | <\/span><span style=\"color: #D19A66\">200<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">200<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">03<\/span><span style=\"color: #ABB2BF\">  | Electronics      | <\/span><span style=\"color: #D19A66\">75<\/span><span style=\"color: #ABB2BF\">           | <\/span><span style=\"color: #D19A66\">325<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">-<\/span><span style=\"color: #D19A66\">02<\/span><span style=\"color: #ABB2BF\">  | Furniture        | <\/span><span style=\"color: #D19A66\">300<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">500<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Use Case: Sales Performance Analysis<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s apply what we&#8217;ve learned to a real-world scenario. Imagine you&#8217;re an analyst at an e-commerce company, and you need to track sales performance over time. You want to see not just daily sales, but also the cumulative sales for the year to date.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how you might approach this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    DATE_TRUNC('day', order_date) AS sale_date,\n    SUM(order_amount) AS daily_sales,\n    SUM(SUM(order_amount)) OVER (\n        ORDER BY DATE_TRUNC('day', order_date)\n    ) AS cumulative_sales\nFROM \n    orders\nWHERE \n    EXTRACT(YEAR FROM order_date) = 2023\nGROUP BY \n    DATE_TRUNC('day', order_date)\nORDER BY \n    sale_date;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, order_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> sale_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> daily_sales,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_amount)) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, order_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cumulative_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> order_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2023<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, order_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_date;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query does a few things:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>It groups sales by day using <code>DATE_TRUNC('day', order_date)<\/code>.<\/li>\n\n\n\n<li>It calculates daily sales with a simple <code>SUM(order_amount)<\/code>.<\/li>\n\n\n\n<li>It then calculates the cumulative sum of these daily sales.<\/li>\n\n\n\n<li>We&#8217;re filtering for only 2023 orders with the <code>WHERE<\/code> clause.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The result gives us a clear picture of both daily performance and overall progress throughout the year.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">SQL Cumulative Sum with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but sometimes more complex operations, like calculating cumulative sums, require extra attention.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze the results of your cumulative sum operations.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, Five lets you write custom JavaScript and TypeScript functions, providing the flexibility to implement custom business logic that can interact with your cumulative data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure. This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Techniques: Moving Averages and Percent of Total<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Cumulative sums are just the beginning. Once you&#8217;re comfortable with window functions, you can use similar techniques for other calculations. Let&#8217;s look at a couple of advanced examples.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Moving Average<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">A moving average can smooth out daily fluctuations and help identify trends. Here&#8217;s how to calculate a 7-day moving average of sales:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    order_date,\n    order_amount,\n    AVG(order_amount) OVER (\n        ORDER BY order_date\n        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n    ) AS moving_avg_7day\nFROM \n    orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(order_amount) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> order_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">6<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> moving_avg_7day<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>ROWS BETWEEN 6 PRECEDING AND CURRENT ROW<\/code> clause defines our window as the current row and the 6 rows before it, giving us a 7-day window.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Percent of Total<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes, you want to know what percent of the total each row represents. Here&#8217;s how to calculate that:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_category,\n    order_amount,\n    order_amount \/ SUM(order_amount) OVER () * 100 AS percent_of_total\nFROM \n    orders;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    order_amount \/ <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_amount) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> () * <\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> percent_of_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates each order&#8217;s percentage of the grand total. The empty <code>OVER ()<\/code> clause means we&#8217;re considering all rows for the total sum.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While cumulative sums are incredibly useful, they can be computationally expensive, especially on large datasets. Here are a few tips to keep in mind:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Indexing<\/strong>: Ensure you have appropriate indexes on the columns you&#8217;re ordering by in your <code>OVER<\/code> clause.<\/li>\n\n\n\n<li><strong>Materialized Views<\/strong>: If you&#8217;re frequently running cumulative sum queries on static or slowly changing data, consider using materialized views to pre-calculate results.<\/li>\n\n\n\n<li><strong>Partitioning<\/strong>: For very large tables, consider partitioning your data. This can significantly speed up queries that only need to access a subset of the data.<\/li>\n\n\n\n<li><strong>Optimizing Date Handling<\/strong>: When working with dates, try to avoid functions in your <code>WHERE<\/code> clause that prevent the use of indexes. For example, <code>WHERE YEAR(date) = 2023<\/code> might prevent index usage, while <code>WHERE date &gt;= '2023-01-01' AND date &lt; '2024-01-01'<\/code> allows it.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Wrapping Up<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">From tracking progress over time to calculating moving averages and percentages, these techniques allow you to extract valuable insights from your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, the key to mastering cumulative sums is understanding window functions. Once you&#8217;ve got that down, you&#8217;ll find yourself reaching for these tools again and again in your data analysis work.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL cumulative sum is a crucial technique for data analysis and reporting. It allows you to calculate running totals, track progress over time, and uncover trends in your data. In this article, we&#8217;ll dive deep into the world of cumulative sums in SQL, exploring various methods and real-world applications. What is a Cumulative Sum? A [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4121,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4119","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Cumulative-Sum-A-Practical-Guide-1.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"SQL cumulative sum is a crucial technique for data analysis and reporting. It allows you to calculate running totals, track progress over time, and uncover trends in your data. In this article, we&#8217;ll dive deep into the world of cumulative sums in SQL, exploring various methods and real-world applications. What is a Cumulative Sum? A&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4119"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4119\/revisions"}],"predecessor-version":[{"id":17821,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4119\/revisions\/17821"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4121"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4119"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4119"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}