SQL query aggregate function in WHERE clause

Question

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.

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. What I am trying to do is inside the subquery, I am getting a list of average day group by each user. Then in the outer query, I am getting the minimum one from that list. Any ideas how to achieve this? — QWERTY, Oct 25 '16 at 07:27
Yeah it is. But for simplicity I removed it. I even renamed the column name as if I were to use my original table name, it will be very confusing — QWERTY, Oct 25 '16 at 07:29
The error message is your answer. You cannot do it that way; you need to find a different way. Perhaps a CTE for your inner query; or a simple sub query with the WHERE outside, or ... — Peter Smith, Oct 25 '16 at 07:33
Try using CROSS APPLY instead of JOIN for the derived data. It's a sort of inline CTE. — Peter Smith, Oct 25 '16 at 10:37
[Here](http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join) is a discussion on `CROSS APPLY` which might help. — Peter Smith, Oct 26 '16 at 07:09

Nitin · Accepted Answer · 2016-10-25T12:07:31.593

3

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);

edited Oct 25 '16 at 12:07

answered Oct 25 '16 at 07:45

Nitin

290
2
12

Sorry but the avg_date column is not recognized. Also, there is a syntax error at the last bracket of second select statement – QWERTY Oct 25 '16 at 07:47
Do you have any ideas? – QWERTY Oct 25 '16 at 08:37
Try "having" insted of "where" in the outer query. – Nitin Oct 25 '16 at 09:11
I have update the query. Also, added a alternative query. Let me know if it works for you. – Nitin Oct 25 '16 at 09:21
Sorry but is there some bracket misplaced? The error is highlighting at the last bracket saying Execting ID or AS quoted_id – QWERTY Oct 25 '16 at 10:51
You are using this in SQL Server ? – Nitin Oct 25 '16 at 10:56
yeah I am using Microsoft SQL Server 2014 – QWERTY Oct 25 '16 at 10:58
The query I have given is for Oracle SQL. Will post another answer below for SQL Server. Give me 5 mins. – Nitin Oct 25 '16 at 11:15
I have added for SQL Server. Please check now. – Nitin Oct 25 '16 at 11:24
I have having syntax error at the INNER JOIN part: (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) – QWERTY Oct 25 '16 at 11:27
There is indeed errors in your query. Acutally, I just answered what you asked(i.e., where condition). The problem is your join query, it is wrong there should be "on" or "where" condition based on which the join will happen, also add a from keyword before inner join and add table1 as well. – Nitin Oct 25 '16 at 11:45
Sorry I think I missed out one FROM for the inner query, now its working fine for inner query. The problem now is at the SELECT after the ALL. Can you actually wrap it with the inner query as what you did above? – QWERTY Oct 25 '16 at 11:48
Foget to add inner_table.avg_date. Just add that in the SELECT after the ALL.(i.e., WHERE avg_date <= ALL(SELECT inner_table.avg_date from.... – Nitin Oct 25 '16 at 12:07
Hmm it did returned me something but it's not the expect output as I think this query perform avg multiple times until it is returning me with -ve value – QWERTY Oct 25 '16 at 12:17
So, that means there is something wrong with the logic. I may be able to help you with it over chat that I will be able to do only after one hour or try to debug the logic, execute only the inner query and see what results its returning and so on. – Nitin Oct 25 '16 at 12:23

SQL query aggregate function in WHERE clause

1 Answers1