WHERE clause unless it is in a subquery contained in a HAVING clause or a select list

Question

I am trying to execute the following query. And I am facing the above error.

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.

SELECT student_id, 
       class_id, 
       item_id, 
       (SELECT stock 
        FROM   dbo.daily_closing_stock_details 
        WHERE  stock_reporting_date = Min(stock_reporting_date) 
               AND item_id = dcs.non_mrp_item_id) AS Stock 
FROM   dbo.daily_closing_stock_details 
GROUP  BY student_id, 
          sap_customer_id, 
          item_id 

Answer 1

Try this one:

SELECT student_id, 
   class_id, 
   item_id,
   (SELECT stock 
    FROM   dbo.daily_closing_stock_details dcs
    WHERE item_id = dcs.non_mrp_item_id 
    HAVING stock_reporting_date = Min(stock_reporting_date)) as Stock 
FROM   dbo.daily_closing_stock_details 
GROUP  BY student_id, 
      sap_customer_id, 
      item_id 

Answer 2

Your subquery must return only one row and one column and then aggregate the value. See changes

SELECT student_id, 
       class_id, 
       item_id, 
       sum((SELECT sum(stock) stock
        FROM   dbo.daily_closing_stock_details 
        WHERE  stock_reporting_date = stock_reporting_date 
               AND item_id = dcs.non_mrp_item_id)) AS Stock 
FROM   dbo.daily_closing_stock_details 
GROUP  BY student_id, 
          sap_customer_id, 
          item_id 

Answer 3

You cannot filter the the results with aggregate function in WHERE clause you need to use HAVING for this for more consistency your virtual column from subquery should return one result for this put TOP 1

SELECT student_id, 
       class_id, 
       item_id, 
       (SELECT TOP 1 stock 
        FROM   dbo.daily_closing_stock_details 
        HAVING stock_reporting_date = Min(stock_reporting_date) 
               AND item_id = dcs.non_mrp_item_id) AS Stock 
FROM   dbo.daily_closing_stock_details 
GROUP  BY student_id, 
          sap_customer_id, 
          item_id 

Answer 4

You have to use aliases otherwise SQL Server assumes that stock_reporting_date column is part of subquery table (y in this case):

SELECT x.student_id, 
       x.class_id, 
       x.item_id, 
       (SELECT y.stock 
        FROM   dbo.daily_closing_stock_details y
        WHERE  y.stock_reporting_date = Min(x.stock_reporting_date) 
               AND y.item_id = x.item_id) AS Stock 
        -- I used x.item_id column instead of non_mrp_item_id
        -- If you have to use x.non_mrp_item_id instead of x.item_id then you need to replace this column within GROUP BY clause 
FROM   dbo.daily_closing_stock_details x
GROUP  BY x.student_id, 
          x.sap_customer_id, 
          x.item_id 

Also, in this case you need an UNIQUE index on item_id and stock_reporting_date columns otherwise you need to use an aggregate function (MIN, MAX, etc: (SELECT MAX(y.stock) ...) or TOP ((SELECT TOP(1) y.stock ... ORDER BY ...)) within subquery.

Answer 5

don't know why you use group by.you can remove in my query.Also in your query

AND item_id = dcs.non_mrp_item_id

is not clear,you can include it .just make it work.

Select student_id, 
   class_id, 
   item_id,
    stock 
FROM   dbo.daily_closing_stock_details 
WHERE  stock_reporting_date =(select  Min(stock_reporting_date) FROM   dbo.daily_closing_stock_details GROUP  BY student_id, 
          sap_customer_id, 
          item_id  )