<html><head><meta name="color-scheme" content="light dark"></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">"use strict";(self.webpackChunk_databricks_docs=self.webpackChunk_databricks_docs||[]).push([["74612"],{50481:function(e,n,a){a.r(n),a.d(n,{metadata:()=&gt;t,contentTitle:()=&gt;c,default:()=&gt;d,assets:()=&gt;r,toc:()=&gt;l,frontMatter:()=&gt;i});var t=JSON.parse('{"id":"sql/language-manual/functions/aggregate","title":"aggregate function","description":"Learn the syntax of the aggregate function of the SQL language in Databricks SQL and Databricks Runtime.","source":"@site/docs/sql/language-manual/functions/aggregate.md","sourceDirName":"sql/language-manual/functions","slug":"/sql/language-manual/functions/aggregate","permalink":"/gcp/en/sql/language-manual/functions/aggregate","draft":false,"unlisted":false,"tags":[],"version":"current","lastUpdatedAt":1696896000000,"frontMatter":{"description":"Learn the syntax of the aggregate function of the SQL language in Databricks SQL and Databricks Runtime.","last_update":{"date":"2023-10-10T00:00:00.000Z"},"pagination_next":null,"pagination_prev":null,"title":"aggregate function"},"sidebar":"docsSidebar"}'),A=a("56906"),s=a("60793");let i={description:"Learn the syntax of the aggregate function of the SQL language in Databricks SQL and Databricks Runtime.",last_update:{date:new Date("2023-10-10T00:00:00.000Z")},pagination_next:null,pagination_prev:null,title:"aggregate function"},c="aggregate function",r={},l=[{value:"Syntax",id:"syntax",level:2},{value:"Arguments",id:"arguments",level:2},{value:"Returns",id:"returns",level:2},{value:"Examples",id:"examples",level:2},{value:"Related functions",id:"related-functions",level:2}];function g(e){let n={a:"a",code:"code",h1:"h1",h2:"h2",header:"header",img:"img",li:"li",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,s.a)(),...e.components};return(0,A.jsxs)(A.Fragment,{children:[(0,A.jsx)(n.header,{children:(0,A.jsxs)(n.h1,{id:"aggregate-function",children:[(0,A.jsx)(n.code,{children:"aggregate"})," function"]})}),"\n",(0,A.jsxs)(n.p,{children:[(0,A.jsx)(n.strong,{children:"Applies to:"})," ",(0,A.jsx)(n.img,{alt:"check marked yes",src:a(3977).Z+"",width:"22",height:"28"})," ","Databricks SQL"," ",(0,A.jsx)(n.img,{alt:"check marked yes",src:a(3977).Z+"",width:"22",height:"28"})," ","Databricks Runtime"]}),"\n",(0,A.jsxs)(n.p,{children:["Aggregates elements in an array using a custom aggregator. This function is a synonym for ",(0,A.jsxs)(n.a,{href:"/gcp/en/sql/language-manual/functions/reduce",children:[(0,A.jsx)(n.code,{children:"reduce"})," function"]}),"."]}),"\n",(0,A.jsx)(n.h2,{id:"syntax",children:"Syntax"}),"\n",(0,A.jsx)(n.pre,{children:(0,A.jsx)(n.code,{children:"aggregate(expr, start, merge [, finish])\n"})}),"\n",(0,A.jsx)(n.h2,{id:"arguments",children:"Arguments"}),"\n",(0,A.jsxs)(n.ul,{children:["\n",(0,A.jsxs)(n.li,{children:[(0,A.jsx)(n.code,{children:"expr"}),": An ARRAY expression."]}),"\n",(0,A.jsxs)(n.li,{children:[(0,A.jsx)(n.code,{children:"start"}),": An initial value of any type."]}),"\n",(0,A.jsxs)(n.li,{children:[(0,A.jsx)(n.code,{children:"merge"}),": A lambda function used to aggregate the current element."]}),"\n",(0,A.jsxs)(n.li,{children:[(0,A.jsx)(n.code,{children:"finish"}),": An optional lambda function used to finalize the aggregation."]}),"\n"]}),"\n",(0,A.jsx)(n.h2,{id:"returns",children:"Returns"}),"\n",(0,A.jsxs)(n.p,{children:["The result type matches the result type of the ",(0,A.jsx)(n.code,{children:"finish"})," lambda function if exists or ",(0,A.jsx)(n.code,{children:"start"}),"."]}),"\n",(0,A.jsxs)(n.p,{children:["Applies an expression to an initial state and all elements in the array, and reduces this to a single state. The final state is converted into the final result by applying a ",(0,A.jsx)(n.code,{children:"finish"})," function."]}),"\n",(0,A.jsxs)(n.p,{children:["The ",(0,A.jsx)(n.code,{children:"merge"})," function takes two parameters. The first being the accumulator, the second the element to be aggregated.\nThe accumulator and the result must be of the type of ",(0,A.jsx)(n.code,{children:"start"}),".\nThe optional ",(0,A.jsx)(n.code,{children:"finish"})," function takes one parameter and returns the final result."]}),"\n",(0,A.jsx)(n.h2,{id:"examples",children:"Examples"}),"\n",(0,A.jsx)(n.pre,{children:(0,A.jsx)(n.code,{className:"language-sql",metastring:"title='SQL'",children:"&gt; SELECT aggregate(array(1, 2, 3), 0, (acc, x) -&gt; acc + x);\n 6\n&gt; SELECT aggregate(array(1, 2, 3), 0, (acc, x) -&gt; acc + x, acc -&gt; acc * 10);\n 60\n\n&gt; SELECT aggregate(array(1, 2, 3, 4),\n                   named_struct('sum', 0, 'cnt', 0),\n                   (acc, x) -&gt; named_struct('sum', acc.sum + x, 'cnt', acc.cnt + 1),\n                   acc -&gt; acc.sum / acc.cnt) AS avg\n 1.5\n"})}),"\n",(0,A.jsx)(n.h2,{id:"related-functions",children:"Related functions"}),"\n",(0,A.jsxs)(n.ul,{children:["\n",(0,A.jsx)(n.li,{children:(0,A.jsxs)(n.a,{href:"/gcp/en/sql/language-manual/functions/array",children:[(0,A.jsx)(n.code,{children:"array"})," function"]})}),"\n",(0,A.jsx)(n.li,{children:(0,A.jsxs)(n.a,{href:"/gcp/en/sql/language-manual/functions/reduce",children:[(0,A.jsx)(n.code,{children:"reduce"})," function"]})}),"\n"]})]})}function d(e={}){let{wrapper:n}={...(0,s.a)(),...e.components};return n?(0,A.jsx)(n,{...e,children:(0,A.jsx)(g,{...e})}):g(e)}},3977:function(e,n,a){a.d(n,{Z:function(){return t}});let t="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABYAAAAcCAYAAABlL09dAAAABGdBTUEAALGPC/xhBQAAACBjSFJNAAB6JgAAgIQAAPoAAACA6AAAdTAAAOpgAAA6mAAAF3CculE8AAAAhGVYSWZNTQAqAAAACAAFARIAAwAAAAEAAQAAARoABQAAAAEAAABKARsABQAAAAEAAABSASgAAwAAAAEAAgAAh2kABAAAAAEAAABaAAAAAAAAAEgAAAABAAAASAAAAAEAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAFqADAAQAAAABAAAAHAAAAAClq0b4AAAACXBIWXMAAAsTAAALEwEAmpwYAAACyGlUWHRYTUw6Y29tLmFkb2JlLnhtcAAAAAAAPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyIgeDp4bXB0az0iWE1QIENvcmUgNi4wLjAiPgogICA8cmRmOlJERiB4bWxuczpyZGY9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjItcmRmLXN5bnRheC1ucyMiPgogICAgICA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0iIgogICAgICAgICAgICB4bWxuczp0aWZmPSJodHRwOi8vbnMuYWRvYmUuY29tL3RpZmYvMS4wLyIKICAgICAgICAgICAgeG1sbnM6ZXhpZj0iaHR0cDovL25zLmFkb2JlLmNvbS9leGlmLzEuMC8iPgogICAgICAgICA8dGlmZjpZUmVzb2x1dGlvbj43MjwvdGlmZjpZUmVzb2x1dGlvbj4KICAgICAgICAgPHRpZmY6UmVzb2x1dGlvblVuaXQ+MjwvdGlmZjpSZXNvbHV0aW9uVW5pdD4KICAgICAgICAgPHRpZmY6WFJlc29sdXRpb24+NzI8L3RpZmY6WFJlc29sdXRpb24+CiAgICAgICAgIDx0aWZmOk9yaWVudGF0aW9uPjE8L3RpZmY6T3JpZW50YXRpb24+CiAgICAgICAgIDxleGlmOlBpeGVsWERpbWVuc2lvbj4zNjwvZXhpZjpQaXhlbFhEaW1lbnNpb24+CiAgICAgICAgIDxleGlmOkNvbG9yU3BhY2U+MTwvZXhpZjpDb2xvclNwYWNlPgogICAgICAgICA8ZXhpZjpQaXhlbFlEaW1lbnNpb24+NDc8L2V4aWY6UGl4ZWxZRGltZW5zaW9uPgogICAgICA8L3JkZjpEZXNjcmlwdGlvbj4KICAgPC9yZGY6UkRGPgo8L3g6eG1wbWV0YT4KgoXBqAAABIFJREFUSA3VlctrXFUcx8/r3hvTNGpISiOULIRKjCAaFQtdzExCk8xkIq1k3LiqqRQEEbcuOv0HBMGFxkdWWbQhSeNMGhJmknEVBFtBFKULH4hUoR2wIZnM3PPw+7uZm0QHrOiqF+7cc885v8/9vc53GHvQLv6vHHaMs8u46brE6M3dz+6fwHzy6qQgwFxuzhwGYV425y2e9/3Ivm1sGE+cns08mppP99FN43ienn/fG6+1eJzYSKhKsqJPvXvqofbHu3LOsZekL07CrS4ygkHVNOwtztnSTr16dTO3WYttYmhz38Fr3uVFnudtcnF0iEv5nvTlAEVqG5Y5sxcxl5wJDxkSnNm6/pqF/K3yy8sbsW1M2/d40k3KOT5nhgvjF5hg05Q5vas1h2sYC7i6t9dFOcWv094jQcBs9OHzpWxxJmYQPCoO5YmgqfnRs/Bo2tSNw01QhT0KSNpHYMILonoPB4Hd1aumbr/CBz5Nzo9OECPOOTUObXaJQrZbMXfTWXfCaktdEFUez79cjjw96itr3Hp5pu1MarJ2XLS7dWA6d3bCgc3capWYYnB6kLxiQpuLIpAnTGhCvBK0pY0I6gPqtF37beaHsfzAgFvPFX8Nt/SUd8Q73uari8RKVBKoBA1mEm3yWMfn3LEXKAWYIjCtUZ9G6QI09Dt9D9FcL6WLE5iPentoIX2SefyaUKLfGfvF9q1qcvPtzVpkpH31GKrej+pb4BRXXHMp6si3gN828rQz8ExoC4BmaQ5gllwafQLQNS54v6kZA+snZV9XL61FYN+T3TA/SrlWRzxqrSnXsCNIvgVaUE5dwyyWM8WJfD5PsbjhYqZfcFlGgft0TYcoqCCGb3Q3gaP8GsmdREbhobTb4asb51ZmaTFxZWREdXgr+NBiabyYi3v1TDHzFNp6Db3ca3ZNCLjHHaco9loSg8hjs6vvcsnuMfSsE6KfoGPXx4LKK6sl03Av6h+3ztNcdHgK408b68pg9No6Cs2ZhyXnqCUF32pIdYf27oF/377NpPieGkG1q3eSS+n3V9Ir9cEvX/c2zi7f6OnpqdHmVCH7LHeuBAeORVAWQcnMSZ8zKfl35ufq7Qg8+OGgR1VETgsykCy819jx2r03ktfSH9x4bjpEw/ukbkOfZZ9nzpYQazeKrJueEoO4RviKQUMKxCLtQOzYgt9EIYED0nHT4oCgULs4TW2mpj8pTyxPJRZGTwspl5GpThsC2qxNk2qgHegf/osLvWfK5xbvEpM0wNExrGQrd1zdvAklQxfzIPyjUUcErw0tZRYE51dg2gqFnMBekiiRLUGjIw3mfhVj6TssQihqXQQqsCHOggOEDg7iBgxyB2VqU4o0ylp+YT1b+DhmUCRR8WhAGkztBJX6yDT0MLTgW9XhB4gJbYjABI4NXBe+ECiwwpqCrnxjjRkiKNkSg1h07Xu898pY/NUWoXdNoedNobcQ+p8g9M1iHYbGrJZnLH3xQvTXNJvqS+H+z39NMQzP//Vn2pKKQ+CDIZXrcjNtl6LitUjqweYHdfQnxCZNMo0VXxYAAAAASUVORK5CYII="},60793:function(e,n,a){a.d(n,{Z:function(){return c},a:function(){return i}});var t=a(24854);let A={},s=t.createContext(A);function i(e){let n=t.useContext(s);return t.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function c(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(A):e.components||A:i(e.components),t.createElement(s.Provider,{value:n},e.children)}}}]);</pre></body></html>