How to use a SUM aggregate with a group By in dql

Users Dgraph
,
1

Hi everyone, i have this object below:

type GoogleBot {
    key: String! @id @search
    domain: String! @search
    date: DateTime
    count: Int!
    week: Int! @search
    year: Int! @search
}

it’s a time series data that display for different domains the number of GoogleBot visits.

i want to create a graph with different aggregated values for all domains on a specific range.

in sql i’ll do the following request but i can’t figure out on how to do that with DQL

select sum(count) as sum, year,week from Googlebots where date > start_date and date <= NOW() group by year, week

or

select sum(count) as sum, year,week from Googlebots where date > start_date and date <= NOW() and domains ="mydomain.com" group by year, week

2

To search for type GoogleBot you need to convert the type to a predicate using Type(GoogleBot)

{
	q(func: type(GoogleBot)) @groupby(GoogleBot.week){
		sum(GoogleBot.count)
  }
}

From Anand Chandrashekar (support Dgraph)

Here is an example. Let’s put in the following two mutations.

mutation MyMutation {
  addGoogleBot(input: {key: "card", domain: "abc.com", count: 40, week: 10, year: 2021}) {
    numUids
  }
}

mutation MyMutation {
  addGoogleBot(input: {key: "card-1", domain: "abc.com", count: 60, week: 10, year: 2021}) {
    numUids
  }
}

You can then fire the groupby query as below.

{
	q(func: type(GoogleBot)) @groupby(GoogleBot.week){
		sum(GoogleBot.count)
  }
}

Response:

{
 "data": {
    "q": [
      {
        "@groupby": [
          {
            "GoogleBot.week": 10,
            "sum(GoogleBot.count)": 100
          }
        ]
      }
    ]
  }
}
1 Like
3

Hi Denis, thanks for sharing the answer here. Please let us know if you need any help.