Question 137373 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How does `count` aggregation function in Apache AGE work?

This is the data setup from documentation:

SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);

and this is the query:

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);

Output:

 count | key
-------+-----
 12    | 6
(1 row)

I don’t understand how the count function works exactly, where is the grouping key in this example is it the (x.a + x.b + x.c) part or is it the , x.a + x.b + x.c part, then how does count work to yield the above output?

Tags:

2

Answers


  1. 0

    In your query, since you used count(*) without any grouping clause, your count functions will calculate the number of rows in the result set and since there are three rows, each count function returns a value of 3.

    Since the aggregate of x.a + x.b + x.c is 6, and the two count functions is equal to 6, count result is equal to 12

    The value of key result (x.a + x.b + x.c) is 6

    Hope this helps

    Login or Signup to reply.
  2. 0

    You can better understand how this query works if you change some values. For example if you create your initial dataset like this:

    SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 1, b: 1, c: 1})
$$) as (a agtype);

    and then run the same query:

    SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);

    you will get this output:

     count | key 
-------+-----
 5     | 3
 10    | 6

    So basically what happened here is that it uses the (x.a + x.b + x.c) as a grouping key.
    One group is the rows that the (x.a + x.b + x.c) + count(*) + count(*) result into 5. If you see our dataset you can see that one of our vertices has different values that (x.a + x.b + x.c) = 3. Since it is only 1 vertex, when the count(*) function is being used it is only going to count 1 vertex twice (because the count(*) function is used twice). Therefore the count in the output is going to be 5, and the key is just the (x.a + x.b + x.c) which is 3.

    The second group is the rows that the (x.a + x.b + x.c) equals to 6. There are 2 rows that satisfy that grouping key so the count(*) function equals to 2 (so the 2 count(*) functions equal to 4). Therefore if we add (x.a + x.b + x.c) + count(*) + count(*) we get 10. And that is the count in our output. For the key it is the same as the first group we just add x.a + x.b + x.c and the resulting key is 6.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search