Bits of .NET

WHERE can’t filter aggregated data. HAVING filters after GROUP BY.

Wrong – WHERE with Aggregate:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
WHERE COUNT(*) > 5  -- ERROR! Can't use aggregate in WHERE
GROUP BY CustomerID;

Correct – HAVING:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;  -- Filters after grouping

-- Only shows customers with more than 5 orders

WHERE vs HAVING:
WHERE: Filters rows BEFORE grouping
HAVING: Filters groups AFTER aggregation

Combined:

SELECT CustomerID, SUM(Total) AS TotalSpent
FROM Orders
WHERE OrderDate >= '2024-01-01'  -- Filter rows first
GROUP BY CustomerID
HAVING SUM(Total) > 1000;  -- Then filter groups