How to make GROUP BY in a cypher query?

Question

I want to translate a SQL query to cypher. Please, is there any solution to make GROUP BY in cypher?

    SELECT dt.d_year, 
           item.i_brand_id          brand_id, 
           item.i_brand             brand, 
           Sum(ss_ext_discount_amt) sum_agg 
    FROM   date_dim dt, 
   store_sales, 
   item 
    WHERE  dt.d_date_sk = store_sales.ss_sold_date_sk 
    AND store_sales.ss_item_sk = item.i_item_sk 
    AND item.i_manufact_id = 427 
    AND dt.d_moy = 11 
    GROUP  BY dt.d_year, 
      item.i_brand, 
      item.i_brand_id 
   ORDER  BY dt.d_year, 
      sum_agg DESC, 
      brand_id;

Gay

Cyper implicitly performs group by; no need to type "group by", just order your columns and include your aggregating function(s). – Commence

I didn't know that, Thank you! – Gay

Here is the Cypher ref sheet => neo4j.com/docs/cypher-refcard/current – Archaism

Answer 1

In Cypher, GROUP BY is done implicitly by all of the aggregate functions. In a WITH/RETURN statement, any columns not part of an aggregate will be the GROUP BY key.

So for example in

MATCH (n:Person)
RETURN COUNT(n), n.name, n.age

The count will count all nodes that have the same name and age. If I instead do

MATCH (n:Person)
RETURN COUNT(n), n.name, MIN(n.age), MAX(n.age)

I will get the count of how many people have the same name, as well as the age range for that name.

Archaism

Just a heads up, I have seen at least one version of Neo4j (not sure which, 3.3 or so) not properly aggregate if the keys come after the aggregating function. So RETURN count(n), n.name, n.age might not behave as expected. Might want to use RETURN n.name, n.age, count(n) just in case. – Useful

Answer 2

As long as you have a limited predictive group (like an enum or so)
you can use the following technique

UNWIND RANGE (1, 10) AS i
WITH COLLECT({ Val: i % 3 }) AS Item
RETURN REDUCE (
    acc = {}, 
    x IN Item | acc {
            .*, 
            V0: CASE x.Val WHEN 0 THEN coalesce(acc.V0 + 1, 1) ELSE acc.V0 END,
            V1: CASE x.Val WHEN 1 THEN coalesce(acc.V1 + 1, 1) ELSE acc.V1 END,
            V2: CASE x.Val WHEN 2 THEN coalesce(acc.V2 + 1, 1) ELSE acc.V2 END 
        }) AS grouping

Another option is:

UNWIND RANGE(1,15) AS i
  WITH i % 4 AS mod, 1 AS unit
  RETURN DISTINCT mod, count(unit) as unit

Prepossession