{"id":11912,"date":"2024-04-29T02:39:13","date_gmt":"2024-04-29T02:39:13","guid":{"rendered":"https:\/\/mysqlcode.com\/?p=11912"},"modified":"2024-04-29T02:39:14","modified_gmt":"2024-04-29T02:39:14","slug":"postgresql-aggregate-functions","status":"publish","type":"post","link":"https:\/\/mysqlcode.com\/postgresql-aggregate-functions\/","title":{"rendered":"PostgreSQL Aggregate Functions: A Detailed Guide"},"content":{"rendered":"\n<p>Aggregate<a href=\"https:\/\/mysqlcode.com\/mysql-window-functions\/\"> window functions<\/a> allow users to efficiently generate output such as comparing specific rows output to average across all rows or calculating cumulative sum by applying the aggregate result to every row of the data. This often helps developers to make their models efficient. In this tutorial, we will learn about the multiple aggregate function with its practical usage in PostgreSQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding Aggregate Function<\/h2>\n\n\n\n<p>Let&#8217;s assume a situation where you are the owner of the grocery store, and as you know being an owner you do have to manage a lot of responsibilities and the most complex one is managing the financial bills. But to get the return on investment you must need to calculate the expenditure ain&#8217;t you?<\/p>\n\n\n\n<p>That&#8217;s where the PostgreSQL aggregate function comes into the picture, to ease all of your tasks! This function performs a calculation on a multiple set of values, and then it returns a single scaler value.<\/p>\n\n\n\n<p>Now, let&#8217;s move to its syntax and know how it actually works.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Syntax<\/h3>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> AGGREGATE_FUNCTION(column_name)\n<span class=\"hljs-keyword\">FROM<\/span> table_name\n<span class=\"hljs-keyword\">WHERE<\/span> condition;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Where AGGREGATE_FUNCTION replaces the specified aggregate function.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Types of PostgreSQL Aggregate Functions<\/h2>\n\n\n\n<p>Here we will learn about the types of aggregate functions:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>AVG()<\/li>\n\n\n\n<li>COUNT()<\/li>\n\n\n\n<li>MIN(), MAX()<\/li>\n\n\n\n<li>SUM()<\/li>\n<\/ul>\n\n\n\n<p>Observe the below table named &#8220;Orders&#8221; to understand the aggregate functions better.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"596\" height=\"240\" src=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Orders-Recprd.png\" alt=\"Orders Recprd\" class=\"wp-image-11920\" style=\"width:565px;height:auto\" srcset=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Orders-Recprd.png 596w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Orders-Recprd-450x181.png 450w\" sizes=\"auto, (max-width: 596px) 100vw, 596px\" \/><figcaption class=\"wp-element-caption\">Orders Record<\/figcaption><\/figure>\n\n\n\n<p><em><strong>Also Read:<\/strong> <a href=\"https:\/\/mysqlcode.com\/sql-ranking-window-functions\/\">An Easy Guide to SQL Ranking Window Functions<\/a><\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. AVG() Function<\/h3>\n\n\n\n<p>Now, let&#8217;s back to our previous assumption, yes, the one where you were the owner of the grocery store. There must be a situation where you wanted to know what is actually your average income.<\/p>\n\n\n\n<p>In the scenarios like this, you can use the AVG() aggregation function. This function is used to get the average value of the records which you have provided.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Syntax<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">AVG<\/span>(<span class=\"hljs-keyword\">column<\/span>) <span class=\"hljs-keyword\">AS<\/span> average_result\n<span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">table<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span> condition;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Example<\/h4>\n\n\n\n<p>Let&#8217;s calculate how much on average you earn from the grocery store.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">AVG<\/span>(order_amount) <span class=\"hljs-keyword\">AS<\/span> average_order_amount\n<span class=\"hljs-keyword\">FROM<\/span> orders;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Output<\/h4>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"823\" height=\"165\" src=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Average-Order-amount-1.png\" alt=\"Average Order Amount\" class=\"wp-image-11924\" srcset=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Average-Order-amount-1.png 823w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Average-Order-amount-1-450x90.png 450w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Average-Order-amount-1-768x154.png 768w\" sizes=\"auto, (max-width: 823px) 100vw, 823px\" \/><figcaption class=\"wp-element-caption\">Average Order Amount<\/figcaption><\/figure>\n\n\n\n<p>As we can see the query returned the average order amount and well that&#8217;s not a bad earning.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2.COUNT() Function<\/h3>\n\n\n\n<p>Now that you know your average income, you must know how many orders you actually got so that you can work on it and increase your productivity.<\/p>\n\n\n\n<p>COUNT() function can do your task effectively. It returns the count of the rows from the result set.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Syntax<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">COUNT<\/span>(*) <span class=\"hljs-keyword\">AS<\/span> count_result\n<span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">table<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span> condition;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Example<\/h4>\n\n\n\n<p>Let&#8217;s know the count of the total order you got.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">COUNT<\/span>(*) <span class=\"hljs-keyword\">AS<\/span> total_orders\n<span class=\"hljs-keyword\">FROM<\/span> orders;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Output<\/h4>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"176\" src=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Tota-Orders-1.png\" alt=\"Total Orders\" class=\"wp-image-11923\" srcset=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Tota-Orders-1.png 602w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Tota-Orders-1-450x132.png 450w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><figcaption class=\"wp-element-caption\">Total Orders<\/figcaption><\/figure>\n\n\n\n<p>As we can see the query returned the count of a total number of orders.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3. MIN() and MAX() Function<\/h3>\n\n\n\n<p>Suppose I am your competitor and my store is just beside yours if I have to compete with you I must know what is your minimum order amount and maximum order amount so that I can analyze it and put the most selling product at the front of the display.<\/p>\n\n\n\n<p>To do so, I will use MIN(), and MAX() functions to ease my task. This function will return the minimum and maximum values from the specified arguments.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Syntax<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">MIN<\/span>(column01) <span class=\"hljs-keyword\">AS<\/span> min_result\n<span class=\"hljs-keyword\">MAX<\/span>(column02) <span class=\"hljs-keyword\">AS<\/span> max_result\n<span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">table<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span> condition;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Example<\/h4>\n\n\n\n<p>So, here is the query to calculate the minimum and maximum amount of your order.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">MIN<\/span>(order_amount) <span class=\"hljs-keyword\">AS<\/span> min_order_amount,\n       <span class=\"hljs-keyword\">MAX<\/span>(order_amount) <span class=\"hljs-keyword\">AS<\/span> max_order_amount\n <span class=\"hljs-keyword\">FROM<\/span> orders;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Output<\/h4>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"162\" src=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/MIN-MAX-Order-amount-1200x162.png\" alt=\"MIN MAX Order Amount\" class=\"wp-image-11922\" srcset=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/MIN-MAX-Order-amount-1200x162.png 1200w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/MIN-MAX-Order-amount-450x61.png 450w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/MIN-MAX-Order-amount-768x104.png 768w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/MIN-MAX-Order-amount.png 1301w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><figcaption class=\"wp-element-caption\">MIN MAX Order Amount<\/figcaption><\/figure>\n\n\n\n<p>As we can see, the query returned the minimum and maximum amount of the order.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4. SUM() Function<\/h3>\n\n\n\n<p>It is good practice to know how much exactly you have earned from your grocery store to calculate how much more you have to invest. This will save you from overspending.<\/p>\n\n\n\n<p>To do so, you can use the SUM() function. This function will calculate the total sum of all values which you have specified.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Syntax<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">SUM<\/span>(<span class=\"hljs-keyword\">column<\/span>) <span class=\"hljs-keyword\">AS<\/span> sum_result\n<span class=\"hljs-keyword\">FROM<\/span> <span class=\"hljs-keyword\">table<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span> condition;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Example<\/h4>\n\n\n\n<p>Let us know your total earnings with the help of the below query:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">SUM<\/span>(order_amount) <span class=\"hljs-keyword\">AS<\/span> total_sales_amount\n <span class=\"hljs-keyword\">FROM<\/span> orders;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Output<\/h4>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"789\" height=\"160\" src=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Total-Sale-Amount.png\" alt=\"Total Sale Amount\" class=\"wp-image-11921\" srcset=\"https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Total-Sale-Amount.png 789w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Total-Sale-Amount-450x91.png 450w, https:\/\/mysqlcode.com\/wp-content\/uploads\/2024\/04\/Total-Sale-Amount-768x156.png 768w\" sizes=\"auto, (max-width: 789px) 100vw, 789px\" \/><figcaption class=\"wp-element-caption\">Total Sale Amount<\/figcaption><\/figure>\n\n\n\n<p>As you can see, the query returned the total amount of the orders placed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>In this tutorial, we have learned about the PostgreSQL Aggregate function with the help of practical usage. It is important for PostgreSQL developers to know about this topic so that they can implement the same on their tasks, which will save a lot of their time and result in highly efficient models during data manipulation. We hope you enjoyed it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Reference<\/h2>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/tagged\/window-functions\" target=\"_blank\" rel=\"noopener\">https:\/\/stackoverflow.com\/questions\/tagged\/window-functions<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Aggregate window functions allow users to efficiently generate output such as comparing specific rows output to average across all rows or calculating cumulative sum by applying the aggregate result to every row of the data. This often helps developers to make their models efficient. In this tutorial, we will learn about the multiple aggregate function [&hellip;]<\/p>\n","protected":false},"author":21,"featured_media":11925,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11912","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/posts\/11912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/comments?post=11912"}],"version-history":[{"count":0,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/posts\/11912\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/media\/11925"}],"wp:attachment":[{"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/media?parent=11912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/categories?post=11912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mysqlcode.com\/wp-json\/wp\/v2\/tags?post=11912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}