May 26, 2018 COUNT, MAX, etc.) in A WHERE clause. Hence we use the HAVING clause instead. Therefore the whole query would be similar to this: SELECT columnname, aggregatefunction(columnname) FROM tablename WHERE columnname operator value GROUP BY columnname HAVING aggregatefunction(columnname) operator value.
HelloThe code below is almost giving me the results i need SELECTROWNUMBER OVER (ORDER BY Orders.StockCode) tempID,Orders.CustNum,Orders.StockCode,Stock.Available,COUNT (Orders.StockCode) OVER (PARTITION BY Orders.StockCode) AS TotalOrdersFROMOrdersINNER JOIN Stock ON Orders.StockCode = Stock.StockCode-WHERE Orders.TotalOrders Stock.AvailableHowever i need to refine it to only show the results where there isn't enough stock. I tried to add in the where clause (which is commented out) but it says 'Invalid column name 'TotalOrders' I understand why its not working but does anyone have any suggestionshow to get round it?Thanks. Columns derived in query cannot be used for Filtering through WHERE clause, though can be used in ORDER BYsee below order of execution, 1. CUBE ROLLUP7. TOPSo, you can use a derived table or CTE.;with CTEas(SELECTROWNUMBER OVER (ORDER BY Orders.StockCode) tempID,Orders.CustNum,Orders.StockCode,Stock.Available,COUNT (Orders.StockCode) OVER (PARTITION BY Orders.StockCode) AS TotalOrdersFROMOrdersINNER JOIN Stock ON Orders.StockCode = Stock.StockCode)select. from CTEWHERE TotalOrders AvailablePlease use Marked as Answer if my post solved your problem and useVote As Helpful if a post was useful.