CODEWITHSUNDEEP
mysqlsqlselectaggregate-functions
Ghilas BELHADJ
Ghilas BELHADJ

Reputation: 14114

Return multiple rows when using AVG

Why does this SQL query return only one result?

select data_value-AVG(data_value) from data

While the expected result is multiple rows with the difference to the average for each data_value.

Upvotes: 0

Views: 651

Answers (1)

Mureinik
Mureinik

Reputation: 312008

You can't treat a single column (data_value, in this case) as both a grouping column and a non grouping column. You'd have to get them from different queries and join them:

SELECT     data_value - avg_data_value
FROM       data
CROSS JOIN (SELECT AVG(data_value) AS avg_data_value
            FROM   data) agg

If you don't want to get the same data_values twice you could add a distinct modifier to the query:

SELECT     DISTINCT data_value - avg_data_value
FROM       data
CROSS JOIN (SELECT AVG(data_value) AS avg_data_value
            FROM   data) agg

Upvotes: 1

Related Questions