Updates for Non-Grouping Columns in Queries with Aggregation Functions
In SQL, when you use aggregation functions with the entire dataset or with groups defined in a Group By clause, the columns the query can use in the Select clause can only be columns that are:
Used to define groups, if any
Used in an aggregate calculation
Note: | This restriction is required to ensure valid data in the query result. Queries in this case return either one row for the entire dataset or one row for each group. A single aggregate result is returned for the functions for each result row. Columns used to define the groups are also guaranteed to have the same value for all dataset rows in a group, and thus have a single known value for the result row for that group. With other columns, however, a single known value is not guaranteed. Note that this restriction does not apply when aggregate functions are used with partitions or windows as each row of the dataset is included in the query result. Aggregate calculations are simply added as new columns to each row. |
RAQL did not enforce this restriction in the previous release. Thus the following query was legal:
select symbol, company, year(date) as yr,
month(datetime) as mnth, avg(high) as average
from stocks
group by symbol, year(datetime), month(datetime)
In this case, the company column in Select is neither used in a calculation nor used in the group definition.
If you have queries with aggregation functions and non-grouping columns selected, you must correct them. In most cases, the solution is simply to remove the offending column. For the previous example, the correct query would be:
select symbol, extract_year(datetime) as yr,
extract_month(datetime) as mnth, avg(high) as average
from stocks
group by symbol, extract_year(datetime),
extract_month(datetime)
In rare cases such as the previous example, the company column which violates this rule also has a guaranteed single value for the corresponding symbol column used for grouping. Because of this, the company column can simply be added to the Group By clause without causing errors:
select symbol, company, extract_year(datetime) as yr,
extract_month(datetime) as mnth, avg(high) as average
from stocks
group by symbol, company, extract_year(datetime),
extract_month(datetime)
