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;

Accepted Answer

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.

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

How to Make Group by in a Cypher Query?

2 Answers

Must Read

Your Answer

Sarah Jenkins

How to Make Group by in a Cypher Query?

2 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Sarah Jenkins