{"id":14606,"date":"2024-03-15T09:36:54","date_gmt":"2024-03-15T09:36:54","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/"},"modified":"2025-08-06T11:00:17","modified_gmt":"2025-08-06T11:00:17","slug":"what-is-the-usage-of-the-over-function-in-sql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/","title":{"rendered":"SQL OVER Function Usage Guide"},"content":{"rendered":"<p>In SQL, the OVER function is used to calculate the results of window functions. Window functions are a type of function used to perform calculations on a specified window or group in query results. The OVER function can be used in conjunction with window functions to calculate and return results for specified groups or windows in query results.<\/p>\n<p>The OVER function is used in the SELECT clause of a SELECT statement, with the following syntax:<\/p>\n<pre class=\"post-pre\"><code>\u7a97\u53e3\u51fd\u6570() OVER (PARTITION BY \u5217\u540d1, \u5217\u540d2,... ORDER BY \u5217\u540d3, \u5217\u540d4,...)\r\n<\/code><\/pre>\n<p>Window functions are a type of aggregate function, such as SUM, AVG, COUNT, and so on. The PARTITION BY clause is used to specify the columns for grouping, while the ORDER BY clause is used to specify the sorting columns.<\/p>\n<p>The purpose of the OVER function is to divide query results into multiple groups or windows, and apply window functions to each group or window for calculation. It can be used to calculate cumulative values, rankings, row numbers, etc. within each group.<\/p>\n<p>For example, consider the following Sales table:<\/p>\n<table>1. Order ID, Product, Quantity, and Price for each item are as follows:<br \/>\n&#8211; Order 1: Product A, Quantity 10, Price $5<br \/>\n&#8211; Order 2: Product A, Quantity 5, Price $10<br \/>\n&#8211; Order 3: Product B, Quantity 8, Price $15<br \/>\n&#8211; Order 4: Product B, Quantity 12, Price $20<\/table>\n<p>To calculate the cumulative sales volume and cumulative sales revenue for each product, window functions can be used.<\/p>\n<pre class=\"post-pre\"><code>SELECT OrderID, Product, Quantity, Price,\r\n    SUM(Quantity) OVER (PARTITION BY Product ORDER BY OrderID) AS TotalQuantity,\r\n    SUM(Quantity * Price) OVER (PARTITION BY Product ORDER BY OrderID) AS TotalPrice\r\nFROM Sales;\r\n<\/code><\/pre>\n<p>Here, we are using the SUM function as a window function to group the data by Product using the PARTITION BY clause and then sorting by OrderID. The results are as follows:<\/p>\n<table>OrderID<br \/>\nProduct<br \/>\nQuantity<br \/>\nPrice<br \/>\nTotal Quantity<br \/>\nTotal Price<\/p>\n<p>1<br \/>\nA<br \/>\n10<br \/>\n5<br \/>\n10<br \/>\n50<\/p>\n<p>2<br \/>\nA<br \/>\n5<br \/>\n10<br \/>\n15<br \/>\n100<\/p>\n<p>3<br \/>\nB<br \/>\n8<br \/>\n15<br \/>\n8<br \/>\n120<\/p>\n<p>4<br \/>\nB<br \/>\n12<br \/>\n20<br \/>\n20<br \/>\n360<\/table>\n<p>It can be seen that the TotalQuantity column calculates the cumulative sales volume of each product, while the TotalPrice column calculates the cumulative sales amount.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL, the OVER function is used to calculate the results of window functions. Window functions are a type of function used to perform calculations on a specified window or group in query results. The OVER function can be used in conjunction with window functions to calculate and return results for specified groups or windows [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[19714,125,765,569,5946],"class_list":["post-14606","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-over-function","tag-sql","tag-sql-functions","tag-sql-tutorial","tag-window-functions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL OVER Function Usage Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn how to use SQL&#039;s OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL OVER Function Usage Guide\" \/>\n<meta property=\"og:description\" content=\"Learn how to use SQL&#039;s OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T09:36:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-06T11:00:17+00:00\" \/>\n<meta name=\"author\" content=\"Olivia Parker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Olivia Parker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\"},\"author\":{\"name\":\"Olivia Parker\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\"},\"headline\":\"SQL OVER Function Usage Guide\",\"datePublished\":\"2024-03-15T09:36:54+00:00\",\"dateModified\":\"2025-08-06T11:00:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\"},\"wordCount\":292,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"OVER function\",\"SQL\",\"SQL Functions\",\"sql tutorial\",\"Window Functions\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\",\"name\":\"SQL OVER Function Usage Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T09:36:54+00:00\",\"dateModified\":\"2025-08-06T11:00:17+00:00\",\"description\":\"Learn how to use SQL's OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL OVER Function Usage Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\",\"name\":\"Olivia Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"caption\":\"Olivia Parker\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL OVER Function Usage Guide - Blog - Silicon Cloud","description":"Learn how to use SQL's OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"SQL OVER Function Usage Guide","og_description":"Learn how to use SQL's OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.","og_url":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T09:36:54+00:00","article_modified_time":"2025-08-06T11:00:17+00:00","author":"Olivia Parker","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Olivia Parker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/"},"author":{"name":"Olivia Parker","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9"},"headline":"SQL OVER Function Usage Guide","datePublished":"2024-03-15T09:36:54+00:00","dateModified":"2025-08-06T11:00:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/"},"wordCount":292,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["OVER function","SQL","SQL Functions","sql tutorial","Window Functions"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/","url":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/","name":"SQL OVER Function Usage Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T09:36:54+00:00","dateModified":"2025-08-06T11:00:17+00:00","description":"Learn how to use SQL's OVER function with window functions. Syntax, PARTITION BY, ORDER BY explained with examples.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/what-is-the-usage-of-the-over-function-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL OVER Function Usage Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9","name":"Olivia Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","caption":"Olivia Parker"},"url":"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/14606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=14606"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/14606\/revisions"}],"predecessor-version":[{"id":158641,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/14606\/revisions\/158641"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=14606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=14606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=14606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}