Reputation: 2325
I got some question regarding MSSQL statement. So basically this is my SQL query:
SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
WHERE avg_date <= ALL
(SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
GROUP BY table1.col1);
What I am trying to do is inside the subquery, I am getting the list of average date group by each user. The example data returned are (userName, avg_date):
user1 10
user2 20
user3 20
Then after that, from the query outside, I need to find the minimum of the average date returned from the sub query. However, by doing this, I am getting and error message as from the query outside, I am comparing 1 column only whereby the subquery is returning 2 columns.
The error message is An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
However, inside my subquery I need the GROUP BY each user so I cannot simply SELECT avg.
Any ideas how to fix this?
Thanks in advance.
Upvotes: 0
Views: 3156
Reputation: 290
Try this
SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
HAVING avg_date <= (SELECT avg_date from
(SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
GROUP BY table1.col1)
);
Alternative:
SELECT * FROM
(
SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
) outer_table
WHERE avg_date <= ALL(SELECT avg_date from(SELECT table1.col1,avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
GROUP BY table1.col1));
Edited for SQL Server
SELECT * FROM
(
SELECT table1.col1, avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
) outer_table
WHERE avg_date <= ALL(SELECT inner_table.avg_date from
(SELECT table1.col1,avg(datediff(dd, table2.date, table3.date)) as avg_date
INNER JOIN table2
INNER JOIN table3
GROUP BY table1.col1) inner_table);
Upvotes: 3