Is there a way to modify the aggregate functions in SQL queries?

[w1992wishes]

Problem

For the SQL API, if the defined metric is 'sum', such as: Is there a way to support the query "select max(number)"?

Related Cube.js schema

number: {
  sql: `number`,
  type: `sum`
}

[igorlukanin]

Hi @w1992wishes 👋

Long story short, it's currently not supported. You can see the supported aggregation functions for measures of different types in this table: https://cube.dev/docs/product/apis-integrations/sql-api#aggregated-and-non-aggregated-queries

That being said, there might be workarounds.

1. Could you please explain your use case? Why would you like to use a different aggregation function?
2. Do you write this SQL by hand or using some BI tool?
3. Which result do you expect to get? Are you expecting something like MAX(SUM(number))?

Cc @paveltiunov for visibility.

[w1992wishes]

Hi @w1992wishes 👋

Long story short, it's currently not supported. You can see the supported aggregation functions for measures of different types in this table: https://cube.dev/docs/product/apis-integrations/sql-api#aggregated-and-non-aggregated-queries

That being said, there might be workarounds.

1. Could you please explain your use case? Why would you like to use a different aggregation function?

We prefer not to define too many metrics. Every time we change an aggregate function, we would need to add a new metric, which increases our workload. This is also the scenario our BI requires.

2. Do you write this SQL by hand or using some BI tool?

BI tool.

3. Which result do you expect to get? Are you expecting something like MAX(SUM(number))?

It's not MAX(SUM(number)), but rather Max(number).

Cc @paveltiunov for visibility.

[igorlukanin]

@w1992wishes Which BI tool, specifically? Could you also share the query that this BI tool generates when you try to apply max to the existing measures?

As I said, allowing to change aggregation functions on measures via the SQL API is currently not supported. However, it might be supported in the future. The details that I ask for would really help inform the implementation.

[w1992wishes]

@w1992wishes Which BI tool, specifically? Could you also share the query that this BI tool generates when you try to apply max to the existing measures?

The query generated by our custom BI tool is: "select MAX(number), dim FROM table GROUP BY dim".

[igorlukanin]

@w1992wishes I see, thanks for sharing! Let's keep this issue for future reference and in case anything changes in the SQL API implementation.

[w1992wishes]

@igorlukanin Thank you very much. I really appreciate your response and effort.

[paveltiunov]

@w1992wishes You can try to define dimension instead of measure and then enable CUBESQL_SQL_PUSH_DOWN=true and see if it works.

[igorlukanin]

@w1992wishes Did Pavel's advice help?

[w1992wishes]

@w1992wishes Did Pavel's advice help?

I tried it and it worked. What is the reason for this difference?