How to make GROUP BY in a cypher query?

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; 

2 Answers

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.

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