Aggregation Functions | Geode Database

<h2 id="aggregation-functions" class="position-relative d-flex align-items-center group"> Aggregation Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-functions" aria-haspopup="dialog" aria-label="Share link: Aggregation Functions"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Aggregation functions compute summary values over groups of rows. This reference covers all aggregation functions available in GQL. <h3 id="overview" class="position-relative d-flex align-items-center group"> Overview <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="overview" aria-haspopup="dialog" aria-label="Share link: Overview"> Share link </button> </h3>Aggregation functions: <ul> <li>Operate on multiple rows to produce single values</li> <li>Support implicit grouping (non-aggregated columns become group keys)</li> <li>Can use DISTINCT to aggregate unique values</li> <li>Handle NULL values according to SQL semantics</li> </ul> <h3 id="count" class="position-relative d-flex align-items-center group"> COUNT <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="count" aria-haspopup="dialog" aria-label="Share link: COUNT"> Share link </button> </h3>Counts rows or non-NULL values. <h4 id="syntax" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">count(*) -- Count all rows count(expression) -- Count non-NULL values count(DISTINCT expr) -- Count distinct non-NULL values </code></pre></div> <h4 id="examples" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count all nodes MATCH (n) RETURN count(*) AS total_nodes; -- Count nodes with label MATCH (p:Person) RETURN count(p) AS person_count; -- Count non-NULL property values MATCH (p:Person) RETURN count(p.email) AS has_email; -- Count distinct values MATCH (p:Person) RETURN count(DISTINCT p.city) AS unique_cities; -- Count relationships MATCH ()-[r:KNOWS]->() RETURN count(r) AS friendship_count; </code></pre></div> <h4 id="grouping-with-count" class="position-relative d-flex align-items-center group"> Grouping with COUNT <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="grouping-with-count" aria-haspopup="dialog" aria-label="Share link: Grouping with COUNT"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count by group MATCH (p:Person) RETURN p.city, count(p) AS population ORDER BY population DESC; -- Count relationships per node MATCH (p:Person)-[:KNOWS]->(friend) RETURN p.name, count(friend) AS friend_count ORDER BY friend_count DESC; </code></pre></div> <h3 id="sum" class="position-relative d-flex align-items-center group"> SUM <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="sum" aria-haspopup="dialog" aria-label="Share link: SUM"> Share link </button> </h3>Computes the sum of numeric values. <h4 id="syntax-1" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-1" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">sum(expression) -- Sum of values sum(DISTINCT expression) -- Sum of distinct values </code></pre></div> <h4 id="examples-1" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-1" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Sum all amounts MATCH (o:Order) RETURN sum(o.amount) AS total_revenue; -- Sum with grouping MATCH (o:Order) RETURN o.category, sum(o.amount) AS category_total ORDER BY category_total DESC; -- Sum distinct values MATCH (t:Transaction) RETURN sum(DISTINCT t.amount) AS unique_amounts_sum; -- Sum relationship properties MATCH (c:Customer)-[p:PURCHASED]->(prod:Product) RETURN c.name, sum(p.quantity * prod.price) AS total_spent; </code></pre></div> <h4 id="null-handling" class="position-relative d-flex align-items-center group"> NULL Handling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="null-handling" aria-haspopup="dialog" aria-label="Share link: NULL Handling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- NULL values are ignored MATCH (p:Person) RETURN sum(p.salary) AS total_salary; -- If some salaries are NULL, they don't affect the sum -- All NULLs returns NULL MATCH (p:Person) WHERE p.bonus IS NULL RETURN sum(p.bonus); -- Returns NULL </code></pre></div> <h3 id="avg" class="position-relative d-flex align-items-center group"> AVG <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="avg" aria-haspopup="dialog" aria-label="Share link: AVG"> Share link </button> </h3>Computes the average (mean) of numeric values. <h4 id="syntax-2" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-2" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">avg(expression) -- Average of values avg(DISTINCT expression) -- Average of distinct values </code></pre></div> <h4 id="examples-2" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-2" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Average age MATCH (p:Person) RETURN avg(p.age) AS average_age; -- Average by group MATCH (e:Employee) RETURN e.department, avg(e.salary) AS avg_salary ORDER BY avg_salary DESC; -- Average distinct values MATCH (r:Review) RETURN avg(DISTINCT r.rating) AS avg_distinct_rating; -- Weighted average (manual calculation) MATCH (p:Product)<-[r:REVIEWED]-(u:User) RETURN p.name, sum(r.rating * r.weight) / sum(r.weight) AS weighted_avg; </code></pre></div> <h3 id="min" class="position-relative d-flex align-items-center group"> MIN <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="min" aria-haspopup="dialog" aria-label="Share link: MIN"> Share link </button> </h3>Returns the minimum value. <h4 id="syntax-3" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-3" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">min(expression) </code></pre></div> <h4 id="examples-3" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-3" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Minimum value MATCH (p:Product) RETURN min(p.price) AS cheapest; -- Minimum by group MATCH (e:Employee) RETURN e.department, min(e.hire_date) AS first_hire; -- Minimum across relationships MATCH (a:City)-[r:ROAD]->(b:City) RETURN a.name, b.name, min(r.distance) AS shortest_route; -- Minimum non-numeric (works on strings, dates) MATCH (u:User) RETURN min(u.username) AS first_alphabetically; </code></pre></div> <h3 id="max" class="position-relative d-flex align-items-center group"> MAX <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="max" aria-haspopup="dialog" aria-label="Share link: MAX"> Share link </button> </h3>Returns the maximum value. <h4 id="syntax-4" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-4" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">max(expression) </code></pre></div> <h4 id="examples-4" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-4" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Maximum value MATCH (o:Order) RETURN max(o.total) AS largest_order; -- Maximum by group MATCH (p:Product) RETURN p.category, max(p.price) AS most_expensive; -- Maximum date MATCH (e:Event) RETURN max(e.date) AS most_recent; -- Combine with other aggregations MATCH (p:Person) RETURN p.city, min(p.age) AS youngest, max(p.age) AS oldest, avg(p.age) AS average; </code></pre></div> <h3 id="collect" class="position-relative d-flex align-items-center group"> COLLECT <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="collect" aria-haspopup="dialog" aria-label="Share link: COLLECT"> Share link </button> </h3>Aggregates values into a list. <h4 id="syntax-5" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-5" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">collect(expression) -- Collect all values collect(DISTINCT expression) -- Collect distinct values </code></pre></div> <h4 id="examples-5" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-5" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Collect names MATCH (p:Person) RETURN collect(p.name) AS all_names; -- Collect by group MATCH (c:Customer)-[:PURCHASED]->(p:Product) RETURN c.name, collect(p.name) AS purchased_products; -- Collect distinct MATCH (p:Person) RETURN collect(DISTINCT p.city) AS unique_cities; -- Collect with ordering (use WITH + ORDER BY first) MATCH (e:Employee) WITH e ORDER BY e.hire_date RETURN collect(e.name) AS employees_by_seniority; </code></pre></div> <h4 id="collect-complex-values" class="position-relative d-flex align-items-center group"> Collect Complex Values <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="collect-complex-values" aria-haspopup="dialog" aria-label="Share link: Collect Complex Values"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Collect maps MATCH (p:Person) RETURN collect({name: p.name, age: p.age}) AS people; -- Collect nodes MATCH (p:Person)-[:KNOWS]->(friend) RETURN p.name, collect(friend) AS friends; -- Collect relationships MATCH (a)-[r:KNOWS]->(b) RETURN collect(r) AS all_friendships; </code></pre></div> <h3 id="stdev-and-stdevp" class="position-relative d-flex align-items-center group"> STDEV and STDEVP <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="stdev-and-stdevp" aria-haspopup="dialog" aria-label="Share link: STDEV and STDEVP"> Share link </button> </h3>Compute standard deviation. <h4 id="syntax-6" class="position-relative d-flex align-items-center group"> Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="syntax-6" aria-haspopup="dialog" aria-label="Share link: Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">stdev(expression) -- Sample standard deviation stdevp(expression) -- Population standard deviation </code></pre></div> <h4 id="examples-6" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-6" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Sample standard deviation MATCH (p:Product) RETURN stdev(p.price) AS price_stdev; -- Population standard deviation MATCH (s:Score) RETURN stdevp(s.value) AS score_stdevp; -- With grouping MATCH (e:Employee) RETURN e.department, avg(e.salary) AS mean, stdev(e.salary) AS std_dev; </code></pre></div> <h3 id="implicit-grouping" class="position-relative d-flex align-items-center group"> Implicit Grouping <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="implicit-grouping" aria-haspopup="dialog" aria-label="Share link: Implicit Grouping"> Share link </button> </h3>Non-aggregated columns in RETURN become group keys (like SQL GROUP BY). <h4 id="how-it-works" class="position-relative d-flex align-items-center group"> How It Works <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="how-it-works" aria-haspopup="dialog" aria-label="Share link: How It Works"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- city is the grouping key MATCH (p:Person) RETURN p.city, count(p) AS count; -- Multiple grouping keys MATCH (e:Employee) RETURN e.department, e.role, count(e) AS count; </code></pre></div> <h4 id="explicit-group-by-optional" class="position-relative d-flex align-items-center group"> Explicit GROUP BY (Optional) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="explicit-group-by-optional" aria-haspopup="dialog" aria-label="Share link: Explicit GROUP BY (Optional)"> Share link </button> </h4>GQL supports explicit GROUP BY for clarity: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (p:Person) RETURN p.city, count(p) AS population GROUP BY p.city ORDER BY population DESC; </code></pre></div> <h3 id="with-for-multi-stage-aggregation" class="position-relative d-flex align-items-center group"> WITH for Multi-Stage Aggregation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="with-for-multi-stage-aggregation" aria-haspopup="dialog" aria-label="Share link: WITH for Multi-Stage Aggregation"> Share link </button> </h3>Use WITH to perform aggregations in stages. <h4 id="two-stage-aggregation" class="position-relative d-flex align-items-center group"> Two-Stage Aggregation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="two-stage-aggregation" aria-haspopup="dialog" aria-label="Share link: Two-Stage Aggregation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- First aggregate per product, then per category MATCH (p:Product)<-[:CONTAINS]-(o:Order) WITH p, count(o) AS sales WITH p.category AS category, count(p) AS product_count, sum(sales) AS total_sales RETURN category, product_count, total_sales ORDER BY total_sales DESC; </code></pre></div> <h4 id="filter-aggregated-results" class="position-relative d-flex align-items-center group"> Filter Aggregated Results <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="filter-aggregated-results" aria-haspopup="dialog" aria-label="Share link: Filter Aggregated Results"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- HAVING equivalent MATCH (c:Customer)-[:PURCHASED]->(p:Product) WITH c, count(p) AS purchase_count WHERE purchase_count >= 5 RETURN c.name, purchase_count; </code></pre></div> <h4 id="aggregate-then-match" class="position-relative d-flex align-items-center group"> Aggregate Then Match <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregate-then-match" aria-haspopup="dialog" aria-label="Share link: Aggregate Then Match"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find top customers, then get their orders MATCH (c:Customer)-[:PURCHASED]->(p) WITH c, count(p) AS purchases ORDER BY purchases DESC LIMIT 10 MATCH (c)-[:PLACED]->(o:Order) RETURN c.name, purchases, collect(o.id) AS order_ids; </code></pre></div> <h3 id="aggregation-with-case" class="position-relative d-flex align-items-center group"> Aggregation with CASE <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-with-case" aria-haspopup="dialog" aria-label="Share link: Aggregation with CASE"> Share link </button> </h3>Conditional aggregation using CASE expressions. <h4 id="conditional-count" class="position-relative d-flex align-items-center group"> Conditional Count <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="conditional-count" aria-haspopup="dialog" aria-label="Share link: Conditional Count"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (o:Order) RETURN count(CASE WHEN o.status = 'completed' THEN 1 END) AS completed, count(CASE WHEN o.status = 'pending' THEN 1 END) AS pending, count(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled; </code></pre></div> <h4 id="conditional-sum" class="position-relative d-flex align-items-center group"> Conditional Sum <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="conditional-sum" aria-haspopup="dialog" aria-label="Share link: Conditional Sum"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (t:Transaction) RETURN sum(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END) AS credits, sum(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END) AS debits; </code></pre></div> <h3 id="null-handling-in-aggregations" class="position-relative d-flex align-items-center group"> NULL Handling in Aggregations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="null-handling-in-aggregations" aria-haspopup="dialog" aria-label="Share link: NULL Handling in Aggregations"> Share link </button> </h3> <h4 id="general-rules" class="position-relative d-flex align-items-center group"> General Rules <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="general-rules" aria-haspopup="dialog" aria-label="Share link: General Rules"> Share link </button> </h4><ul> <li><code>count(*)</code> counts all rows, including those with NULL values</li> <li><code>count(expr)</code> counts rows where expr is not NULL</li> <li><code>sum</code>, <code>avg</code>, <code>min</code>, <code>max</code> ignore NULL values</li> <li>If all values are NULL, numeric aggregations return NULL</li> <li><code>collect</code> includes NULL values in the list</li> </ul> <h4 id="examples-7" class="position-relative d-flex align-items-center group"> Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="examples-7" aria-haspopup="dialog" aria-label="Share link: Examples"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Demonstrate NULL handling MATCH (p:Person) RETURN count(*) AS total_rows, count(p.email) AS has_email, avg(p.salary) AS avg_salary; -- Ignores NULL salaries </code></pre></div> <h3 id="distinct-in-aggregations" class="position-relative d-flex align-items-center group"> DISTINCT in Aggregations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="distinct-in-aggregations" aria-haspopup="dialog" aria-label="Share link: DISTINCT in Aggregations"> Share link </button> </h3>Use DISTINCT to aggregate only unique values. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count unique values MATCH (o:Order) RETURN count(DISTINCT o.customer_id) AS unique_customers; -- Sum distinct (unusual but valid) MATCH (t:Transaction) RETURN sum(DISTINCT t.fee) AS unique_fee_sum; -- Collect distinct MATCH (p:Person) RETURN collect(DISTINCT p.city) AS cities; </code></pre></div> <h3 id="aggregation-over-paths" class="position-relative d-flex align-items-center group"> Aggregation Over Paths <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-over-paths" aria-haspopup="dialog" aria-label="Share link: Aggregation Over Paths"> Share link </button> </h3>Aggregate values from variable-length paths. <h4 id="using-reduce" class="position-relative d-flex align-items-center group"> Using REDUCE <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="using-reduce" aria-haspopup="dialog" aria-label="Share link: Using REDUCE"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Sum values along path MATCH path = (a)-[rels:TRANSFER*1..5]->(b) RETURN a.name, b.name, reduce(total = 0, r IN rels | total + r.amount) AS path_sum; </code></pre></div> <h4 id="aggregate-path-properties" class="position-relative d-flex align-items-center group"> Aggregate Path Properties <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregate-path-properties" aria-haspopup="dialog" aria-label="Share link: Aggregate Path Properties"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Path with relationship aggregation MATCH path = (start)-[r:ROUTE*]->(end) RETURN start.name, end.name, length(path) AS hops, reduce(dist = 0, rel IN r | dist + rel.distance) AS total_distance; </code></pre></div> <h3 id="performance-considerations" class="position-relative d-flex align-items-center group"> Performance Considerations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-considerations" aria-haspopup="dialog" aria-label="Share link: Performance Considerations"> Share link </button> </h3> <h4 id="efficient-aggregation" class="position-relative d-flex align-items-center group"> Efficient Aggregation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="efficient-aggregation" aria-haspopup="dialog" aria-label="Share link: Efficient Aggregation"> Share link </button> </h4><ol> <li> Filter before aggregating <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Good: Filter first MATCH (o:Order) WHERE o.date > date('2026-01-01') RETURN o.category, count(o); -- Less efficient: Aggregate then filter MATCH (o:Order) WITH o.category AS cat, count(o) AS cnt WHERE cnt > 100 RETURN cat, cnt; </code></pre></div></li> <li> Use indexes for grouping keys <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create index on grouping column CREATE INDEX order_category ON Order(category); MATCH (o:Order) RETURN o.category, count(o); </code></pre></div></li> <li> Limit result set <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Get top N MATCH (p:Product)<-[:PURCHASED]-(c:Customer) RETURN p.name, count(c) AS purchases ORDER BY purchases DESC LIMIT 10; </code></pre></div></li> </ol> <h4 id="memory-usage" class="position-relative d-flex align-items-center group"> Memory Usage <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="memory-usage" aria-haspopup="dialog" aria-label="Share link: Memory Usage"> Share link </button> </h4>Large aggregations can consume significant memory: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- collect() stores all values in memory MATCH (n:Node) RETURN collect(n.data); -- May be large! -- Consider pagination instead MATCH (n:Node) RETURN n.data LIMIT 1000; </code></pre></div> <h3 id="common-aggregation-patterns" class="position-relative d-flex align-items-center group"> Common Aggregation Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="common-aggregation-patterns" aria-haspopup="dialog" aria-label="Share link: Common Aggregation Patterns"> Share link </button> </h3> <h4 id="top-n-by-count" class="position-relative d-flex align-items-center group"> Top N by Count <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="top-n-by-count" aria-haspopup="dialog" aria-label="Share link: Top N by Count"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (p:Product)<-[:PURCHASED]-(c:Customer) RETURN p.name, count(c) AS sales ORDER BY sales DESC LIMIT 10; </code></pre></div> <h4 id="percentile-approximate" class="position-relative d-flex align-items-center group"> Percentile (Approximate) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="percentile-approximate" aria-haspopup="dialog" aria-label="Share link: Percentile (Approximate)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Get median (50th percentile) MATCH (p:Product) WITH p ORDER BY p.price WITH collect(p.price) AS prices RETURN prices[size(prices)/2] AS median; </code></pre></div> <h4 id="running-totals-with-collect" class="position-relative d-flex align-items-center group"> Running Totals (with collect) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="running-totals-with-collect" aria-haspopup="dialog" aria-label="Share link: Running Totals (with collect)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (o:Order) WITH o ORDER BY o.date WITH collect({date: o.date, amount: o.amount}) AS orders UNWIND range(0, size(orders)-1) AS i RETURN orders[i].date, reduce(sum = 0, j IN range(0, i) | sum + orders[j].amount) AS running_total; </code></pre></div> <h4 id="pivot-style-results" class="position-relative d-flex align-items-center group"> Pivot-Style Results <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="pivot-style-results" aria-haspopup="dialog" aria-label="Share link: Pivot-Style Results"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (s:Sale) RETURN s.product, sum(CASE WHEN s.quarter = 'Q1' THEN s.amount ELSE 0 END) AS Q1, sum(CASE WHEN s.quarter = 'Q2' THEN s.amount ELSE 0 END) AS Q2, sum(CASE WHEN s.quarter = 'Q3' THEN s.amount ELSE 0 END) AS Q3, sum(CASE WHEN s.quarter = 'Q4' THEN s.amount ELSE 0 END) AS Q4; </code></pre></div> <h4 id="histogrambuckets" class="position-relative d-flex align-items-center group"> Histogram/Buckets <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="histogrambuckets" aria-haspopup="dialog" aria-label="Share link: Histogram/Buckets"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (p:Person) RETURN CASE WHEN p.age < 20 THEN '0-19' WHEN p.age < 40 THEN '20-39' WHEN p.age < 60 THEN '40-59' ELSE '60+' END AS age_group, count(p) AS count ORDER BY age_group; </code></pre></div> <h3 id="aggregation-function-reference" class="position-relative d-flex align-items-center group"> Aggregation Function Reference <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-function-reference" aria-haspopup="dialog" aria-label="Share link: Aggregation Function Reference"> Share link </button> </h3><table> <thead> <tr> <th>Function</th> <th>Description</th> <th>NULL Handling</th> </tr> </thead> <tbody> <tr> <td><code>count(*)</code></td> <td>Count all rows</td> <td>Includes NULLs</td> </tr> <tr> <td><code>count(expr)</code></td> <td>Count non-NULL values</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>sum(expr)</code></td> <td>Sum of values</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>avg(expr)</code></td> <td>Average of values</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>min(expr)</code></td> <td>Minimum value</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>max(expr)</code></td> <td>Maximum value</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>collect(expr)</code></td> <td>Collect into list</td> <td>Includes NULLs</td> </tr> <tr> <td><code>stdev(expr)</code></td> <td>Sample std dev</td> <td>Ignores NULLs</td> </tr> <tr> <td><code>stdevp(expr)</code></td> <td>Population std dev</td> <td>Ignores NULLs</td> </tr> </tbody> </table> <h3 id="related-documentation" class="position-relative d-flex align-items-center group"> Related Documentation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-documentation" aria-haspopup="dialog" aria-label="Share link: Related Documentation"> Share link </button> </h3><ul> <li><a href="/docs/quick-reference/" >GQL Quick Reference</a> - Quick syntax lookup</li> <li><a href="/docs/gql-reference/pattern-matching/" >Pattern Matching</a> - Pattern syntax</li> <li><a href="/docs/gql-tutorial/" >GQL Tutorial</a> - Learn GQL step by step</li> <li><a href="/docs/query/performance-tuning/" >Query Performance Tuning</a> - Optimization</li> <li><a href="/docs/analytics/real-time-analytics/" >Real-Time Analytics</a> - Analytics patterns</li> </ul> <hr> Last Updated: January 28, 2026 Geode Version: v0.2.18+ GQL Compliance: ISO/IEC 39075:2024