Aggregation Functions

count(*)              -- Count all rows
count(expression)     -- Count non-NULL values
count(DISTINCT expr)  -- Count distinct non-NULL values

-- 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;

-- 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;

sum(expression)           -- Sum of values
sum(DISTINCT expression)  -- Sum of distinct values

-- 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;

-- 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

avg(expression)           -- Average of values
avg(DISTINCT expression)  -- Average of distinct values

-- 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;

min(expression)

-- 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;

max(expression)

-- 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;

collect(expression)           -- Collect all values
collect(DISTINCT expression)  -- Collect distinct values

-- 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;

-- 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;

stdev(expression)   -- Sample standard deviation
stdevp(expression)  -- Population standard deviation

-- 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;

-- 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;

MATCH (p:Person)
RETURN p.city, count(p) AS population
GROUP BY p.city
ORDER BY population DESC;

-- 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;

-- HAVING equivalent
MATCH (c:Customer)-[:PURCHASED]->(p:Product)
WITH c, count(p) AS purchase_count
WHERE purchase_count >= 5
RETURN c.name, purchase_count;

-- 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;

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;

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;

-- 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

-- 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;

-- 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;

-- 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;

-- 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;

MATCH (p:Product)<-[:PURCHASED]-(c:Customer)
RETURN p.name, count(c) AS sales
ORDER BY sales DESC
LIMIT 10;

-- 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;

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;

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;

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;