Aggregation Functions in GQL

<h2 id="aggregation-functions-in-gql" class="position-relative d-flex align-items-center group"> Aggregation Functions in GQL <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-in-gql" aria-haspopup="dialog" aria-label="Share link: Aggregation Functions in GQL"> 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 are essential tools for analyzing and summarizing graph data in Geode. They allow you to compute statistical values, count occurrences, collect items into lists, and derive meaningful insights from your graph queries. Geode implements the full set of aggregation functions defined in the ISO/IEC 39075:2024 GQL standard. <h3 id="introduction-to-aggregation" class="position-relative d-flex align-items-center group"> Introduction to 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="introduction-to-aggregation" aria-haspopup="dialog" aria-label="Share link: Introduction to Aggregation"> Share link </button> </h3>Aggregation functions operate on sets of values and return a single computed result. In graph databases, aggregations are particularly powerful because they can summarize data across complex relationship patterns, enabling analytics that would be difficult or impossible with traditional relational approaches. Key characteristics of GQL aggregations: <ul> <li>Implicit Grouping: When aggregation functions are used with non-aggregated expressions, GQL automatically groups by the non-aggregated values</li> <li>NULL Handling: Most aggregation functions ignore NULL values (except COUNT(*))</li> <li>Type Safety: Functions enforce appropriate input types and return predictable result types</li> <li>Performance: Geode optimizes aggregation operations to minimize memory usage and maximize throughput</li> </ul> <h3 id="core-aggregation-functions" class="position-relative d-flex align-items-center group"> Core 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="core-aggregation-functions" aria-haspopup="dialog" aria-label="Share link: Core Aggregation Functions"> Share link </button> </h3> <h4 id="count-counting-elements" class="position-relative d-flex align-items-center group"> COUNT: Counting Elements <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-counting-elements" aria-haspopup="dialog" aria-label="Share link: COUNT: Counting Elements"> Share link </button> </h4>The COUNT function is the most commonly used aggregation, counting the number of rows or non-NULL values. Basic Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count all matched rows COUNT(*) -- Count non-NULL values COUNT(expression) -- Count distinct values COUNT(DISTINCT expression) </code></pre></div>Example: Counting Nodes and Relationships: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count all users in the database MATCH (u:User) RETURN COUNT(*) AS total_users; -- Count users with email addresses MATCH (u:User) RETURN COUNT(u.email) AS users_with_email; -- Count unique countries MATCH (u:User) RETURN COUNT(DISTINCT u.country) AS unique_countries; </code></pre></div>Example: Counting Relationships: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count followers per user MATCH (u:User)<-[:FOLLOWS]-(follower) RETURN u.name, COUNT(follower) AS follower_count ORDER BY follower_count DESC LIMIT 10; -- Count mutual connections MATCH (a:User {name: 'Alice'})-[:FRIEND]-(mutual)-[:FRIEND]-(b:User {name: 'Bob'}) RETURN COUNT(DISTINCT mutual) AS mutual_friends; </code></pre></div>Example: Conditional Counting: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count with conditions using CASE MATCH (p:Product) RETURN COUNT(*) AS total_products, COUNT(CASE WHEN p.price > 100 THEN 1 END) AS premium_products, COUNT(CASE WHEN p.in_stock THEN 1 END) AS available_products; </code></pre></div> <h4 id="sum-calculating-totals" class="position-relative d-flex align-items-center group"> SUM: Calculating Totals <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-calculating-totals" aria-haspopup="dialog" aria-label="Share link: SUM: Calculating Totals"> Share link </button> </h4>The SUM function calculates the total of numeric values. Basic Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">SUM(numeric_expression) SUM(DISTINCT numeric_expression) </code></pre></div>Example: Summing Values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Calculate total revenue MATCH (o:Order)-[:CONTAINS]->(i:OrderItem) RETURN SUM(i.quantity * i.price) AS total_revenue; -- Sum by category MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, SUM(p.price) AS category_value ORDER BY category_value DESC; </code></pre></div>Example: Summing with Grouping: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Monthly sales totals MATCH (o:Order) WHERE o.date >= date('2025-01-01') AND o.date < date('2026-01-01') RETURN o.date.year AS year, o.date.month AS month, SUM(o.total) AS monthly_revenue ORDER BY year, month; </code></pre></div>Example: Running Totals with WITH: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Calculate customer lifetime value MATCH (c:Customer)-[:PLACED]->(o:Order) WITH c, SUM(o.total) AS lifetime_value WHERE lifetime_value > 1000 RETURN c.name, lifetime_value ORDER BY lifetime_value DESC; </code></pre></div> <h4 id="avg-computing-averages" class="position-relative d-flex align-items-center group"> AVG: Computing Averages <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-computing-averages" aria-haspopup="dialog" aria-label="Share link: AVG: Computing Averages"> Share link </button> </h4>The AVG function calculates the arithmetic mean of numeric values. Basic Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">AVG(numeric_expression) AVG(DISTINCT numeric_expression) </code></pre></div>Example: Basic Averages: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Average product price MATCH (p:Product) RETURN AVG(p.price) AS average_price; -- Average rating per product MATCH (p:Product)<-[r:RATED]-(u:User) RETURN p.name, AVG(r.score) AS avg_rating, COUNT(r) AS review_count ORDER BY avg_rating DESC; </code></pre></div>Example: Weighted Averages: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Weighted average rating (by number of helpful votes) MATCH (p:Product)<-[r:REVIEWED]-(u:User) WITH p, SUM(r.rating * r.helpful_votes) AS weighted_sum, SUM(r.helpful_votes) AS total_votes WHERE total_votes > 0 RETURN p.name, weighted_sum / total_votes AS weighted_avg_rating ORDER BY weighted_avg_rating DESC; </code></pre></div>Example: Comparing to Average: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find products priced above average MATCH (p:Product) WITH AVG(p.price) AS avg_price MATCH (p:Product) WHERE p.price > avg_price RETURN p.name, p.price, avg_price ORDER BY p.price DESC; </code></pre></div> <h4 id="min-and-max-finding-extremes" class="position-relative d-flex align-items-center group"> MIN and MAX: Finding Extremes <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-and-max-finding-extremes" aria-haspopup="dialog" aria-label="Share link: MIN and MAX: Finding Extremes"> Share link </button> </h4>MIN and MAX functions find the smallest and largest values respectively. Basic Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MIN(expression) MAX(expression) </code></pre></div>Example: Finding Extreme Values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Price range MATCH (p:Product) RETURN MIN(p.price) AS lowest_price, MAX(p.price) AS highest_price; -- Date ranges MATCH (e:Event) RETURN MIN(e.date) AS first_event, MAX(e.date) AS last_event; </code></pre></div>Example: Min/Max with Grouping: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Price range by category MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, MIN(p.price) AS min_price, MAX(p.price) AS max_price, MAX(p.price) - MIN(p.price) AS price_spread ORDER BY price_spread DESC; </code></pre></div>Example: Finding Records with Min/Max Values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find the most expensive product MATCH (p:Product) WITH MAX(p.price) AS max_price MATCH (p:Product) WHERE p.price = max_price RETURN p.name, p.price; -- More efficient using ORDER BY MATCH (p:Product) RETURN p.name, p.price ORDER BY p.price DESC LIMIT 1; </code></pre></div> <h4 id="collect-building-lists" class="position-relative d-flex align-items-center group"> COLLECT: Building Lists <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-building-lists" aria-haspopup="dialog" aria-label="Share link: COLLECT: Building Lists"> Share link </button> </h4>The COLLECT function aggregates values into a list, preserving individual elements. Basic Syntax: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">COLLECT(expression) COLLECT(DISTINCT expression) </code></pre></div>Example: Collecting Related Entities: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Collect all products purchased by a user MATCH (u:User {name: 'Alice'})-[:PURCHASED]->(p:Product) RETURN u.name, COLLECT(p.name) AS purchased_products; -- Collect with distinct MATCH (u:User)-[:PURCHASED]->(p:Product)-[:IN_CATEGORY]->(c:Category) RETURN u.name, COLLECT(DISTINCT c.name) AS categories_shopped; </code></pre></div>Example: Collecting Complex Structures: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Collect maps of product details MATCH (u:User)-[:PURCHASED]->(p:Product) RETURN u.name, COLLECT({ name: p.name, price: p.price, category: p.category }) AS purchase_details; </code></pre></div>Example: Using COLLECT with Other Aggregations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Summary with collected details MATCH (c:Customer)-[:PLACED]->(o:Order)-[:CONTAINS]->(p:Product) RETURN c.name, COUNT(DISTINCT o) AS order_count, SUM(o.total) AS total_spent, COLLECT(DISTINCT p.name) AS products_purchased; </code></pre></div> <h3 id="advanced-aggregation-patterns" class="position-relative d-flex align-items-center group"> Advanced 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="advanced-aggregation-patterns" aria-haspopup="dialog" aria-label="Share link: Advanced Aggregation Patterns"> Share link </button> </h3> <h4 id="aggregation-with-with-clause" class="position-relative d-flex align-items-center group"> Aggregation with WITH Clause <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-with-clause" aria-haspopup="dialog" aria-label="Share link: Aggregation with WITH Clause"> Share link </button> </h4>The WITH clause enables multi-stage aggregations and filtering on aggregated results. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find users with above-average purchase counts MATCH (u:User)-[:PURCHASED]->(p:Product) WITH u, COUNT(p) AS purchase_count WITH AVG(purchase_count) AS avg_purchases MATCH (u:User)-[:PURCHASED]->(p:Product) WITH u, COUNT(p) AS purchase_count, avg_purchases WHERE purchase_count > avg_purchases RETURN u.name, purchase_count, avg_purchases ORDER BY purchase_count DESC; </code></pre></div> <h4 id="nested-aggregations" class="position-relative d-flex align-items-center group"> Nested 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="nested-aggregations" aria-haspopup="dialog" aria-label="Share link: Nested Aggregations"> Share link </button> </h4>Combine aggregations across different grouping levels. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Average order size per customer, then average across all customers MATCH (c:Customer)-[:PLACED]->(o:Order) WITH c, AVG(o.item_count) AS avg_order_size RETURN AVG(avg_order_size) AS overall_avg_order_size; </code></pre></div> <h4 id="conditional-aggregation" class="position-relative d-flex align-items-center group"> Conditional 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="conditional-aggregation" aria-haspopup="dialog" aria-label="Share link: Conditional Aggregation"> Share link </button> </h4>Use CASE expressions within aggregations for conditional calculations. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Segment analysis MATCH (c:Customer)-[:PLACED]->(o:Order) RETURN SUM(CASE WHEN o.total < 50 THEN 1 ELSE 0 END) AS small_orders, SUM(CASE WHEN o.total >= 50 AND o.total < 200 THEN 1 ELSE 0 END) AS medium_orders, SUM(CASE WHEN o.total >= 200 THEN 1 ELSE 0 END) AS large_orders, AVG(CASE WHEN c.is_premium THEN o.total END) AS avg_premium_order, AVG(CASE WHEN NOT c.is_premium THEN o.total END) AS avg_standard_order; </code></pre></div> <h4 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> </h4>Aggregate values along traversed paths. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Sum weights along shortest paths MATCH path = shortestPath((a:City {name: 'NYC'})-[:CONNECTED_TO*]-(b:City {name: 'LA'})) WITH path, [r IN relationships(path) | r.distance] AS distances RETURN [n IN nodes(path) | n.name] AS route, REDUCE(total = 0, d IN distances | total + d) AS total_distance; </code></pre></div> <h4 id="grouping-sets" class="position-relative d-flex align-items-center group"> Grouping Sets <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-sets" aria-haspopup="dialog" aria-label="Share link: Grouping Sets"> Share link </button> </h4>Perform aggregations at multiple grouping levels simultaneously. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Multi-level summary MATCH (s:Sale)-[:OF_PRODUCT]->(p:Product)-[:IN_CATEGORY]->(c:Category) WITH c.name AS category, p.name AS product, SUM(s.amount) AS sales RETURN category, product, sales UNION ALL MATCH (s:Sale)-[:OF_PRODUCT]->(p:Product)-[:IN_CATEGORY]->(c:Category) WITH c.name AS category, SUM(s.amount) AS sales RETURN category, NULL AS product, sales UNION ALL MATCH (s:Sale) RETURN NULL AS category, NULL AS product, SUM(s.amount) AS sales; </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <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="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="performance-optimization" class="position-relative d-flex align-items-center group"> Performance Optimization <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-optimization" aria-haspopup="dialog" aria-label="Share link: Performance Optimization"> Share link </button> </h4><ol> <li>Filter Before Aggregating: Apply WHERE clauses before aggregation to reduce the data set</li> </ol> <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('2025-01-01') RETURN COUNT(*) AS recent_orders; -- Avoid: Aggregating then filtering (when possible) </code></pre></div><ol start="2"> <li>Use Indexes for Grouping Keys: Ensure properties used in implicit grouping are indexed</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX category_name ON Category(name); MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, COUNT(p); -- Uses index for grouping </code></pre></div><ol start="3"> <li>Limit COLLECT Size: When collecting large datasets, consider limiting results</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (u:User)-[:PURCHASED]->(p:Product) WITH u, COLLECT(p.name)[0..10] AS recent_products RETURN u.name, recent_products; </code></pre></div><ol start="4"> <li>Avoid Redundant Aggregations: Compute aggregations once and reuse with WITH</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Good: Compute once MATCH (p:Product) WITH AVG(p.price) AS avg, STDEV(p.price) AS stddev MATCH (p:Product) WHERE p.price > avg + 2 * stddev RETURN p.name AS outlier; </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><ol> <li>COUNT(*) vs COUNT(expression): Use COUNT(*) to count rows including NULLs</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Count all users MATCH (u:User) RETURN COUNT(*) AS all_users, COUNT(u.email) AS users_with_email; </code></pre></div><ol start="2"> <li>COALESCE for Default Values: Handle NULLs in calculations</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (p:Product) RETURN AVG(COALESCE(p.discount, 0)) AS avg_discount; </code></pre></div> <h4 id="readability-and-maintenance" class="position-relative d-flex align-items-center group"> Readability and Maintenance <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="readability-and-maintenance" aria-haspopup="dialog" aria-label="Share link: Readability and Maintenance"> Share link </button> </h4><ol> <li>Use Meaningful Aliases: Name aggregated columns descriptively</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">RETURN COUNT(*) AS total_transactions, SUM(amount) AS gross_revenue, AVG(amount) AS average_transaction_value; </code></pre></div><ol start="2"> <li>Break Complex Aggregations into Steps: Use WITH for clarity</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Multi-step aggregation MATCH (c:Customer)-[:PLACED]->(o:Order) WITH c, COUNT(o) AS order_count, SUM(o.total) AS total_spent WITH c, order_count, total_spent, total_spent / order_count AS avg_order_value WHERE avg_order_value > 100 RETURN c.name, order_count, total_spent, avg_order_value; </code></pre></div> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <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-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/gql-reference/" >GQL Reference</a> : Complete GQL language reference</li> <li><a href="/tags/with/" >WITH Clause</a> : Query chaining and intermediate results</li> <li><a href="/tags/return/" >RETURN Clause</a> : Specifying query output</li> <li><a href="/tags/where/" >WHERE Clause</a> : Filtering results</li> <li><a href="/tags/query-optimization/" >Query Optimization</a> : Performance tuning</li> <li><a href="/tags/analytics/" >Analytics</a> : Graph analytics and algorithms</li> <li><a href="/tags/functions/" >Functions</a> : GQL built-in functions</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <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="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li>Aggregation Tutorial: <code>/docs/gql-tutorial/aggregations/</code></li> <li>Analytics Guide: <code>/docs/analytics/overview/</code></li> <li>Query Performance: <code>/docs/performance/query-optimization/</code></li> <li>GQL Specification: <code>/docs/gql-reference/specification/</code></li> <li>Functions Reference: <code>/docs/gql-reference/functions/</code></li> </ul>

Popular

Related Articles

Aggregation Functions