CODEWITHSUNDEEP
sql-serversql-server-2008selectsubquery
QWERTY
QWERTY

Reputation: 2325

SQL query aggregate function in WHERE clause

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

Answers (1)

Nitin
Nitin

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

Related Questions