skip to Main Content

In postgreSQL, we can use filter such as-

SELECT column_name,
       SUM(sales) FILTER (WHERE year = 2022) AS total_sales_2022,
       SUM(sales) FILTER (WHERE year = 2023) AS total_sales_2023
FROM sales_table
GROUP BY column_name;

How can we implement this in Apache age? Anyone help me plz.

2

Answers


  1. In apacheAGE we are not dealing with tables, but with nodes and edges.
    Those nodes/edges hold information though their labels and/or properties. For example :

    SELECT * FROM cypher('test_graph', $$
    CREATE (u:Person {name: 'John'})
    CREATE (v:Person {name: 'Jake'})
    CREATE (k:Person {name: 'Alice'})
    CREATE (l:Person {name: 'Anna'}) $$)
    AS (u agtype);
    

    Here we create 4 different nodes with Person as the label, and their respective name as property. If we want to find a node through its label we can do:

    SELECT * FROM cypher ('test_graph', $$
    MATCH (u:Person)
    RETURN u $$)
    as (u agtype);
    

    This will return all the nodes in our graph that belong to the Person label. Similarly we can filter through a property of a node for example:

    SELECT * FROM cypher ('test_graph', $$
    MATCH (u:Person) 
    WHERE u.name = 'John'
    RETURN u $$)
    as (u agtype);
    

    Here this will return only the nodes that have the property name and it equals 'John'.

    Login or Signup to reply.
  2. As per the Doc,

    To calculate aggregated data, Cypher offers aggregation, analogous to SQL’s GROUP BY.

    An equivalent cypher query would be:

    SELECT * FROM cypher('sales', $$
    MATCH (product) WHERE product.year = 2022 WITH product.name as name, count(*) as c, SUM(product.sales) as 
    sales
    RETURN name, sales
    $$) AS (name agtype, sales agtype);
    

    This query will match for the products, filter on the year and apply aggregation using COUNT (analogous to GROUP BY). Then you can apply SUM aggregation function to calculate the total sales for each product.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search