تفاوت WHERE و HAVING در SQL و نحوه‌ی کار GROUP BY

WHERE و HAVING هر دو شرط می‌گذارند، اما در دو مرحله‌ی متفاوت. اگر این تفاوت را بفهمی، خطای معروف «Column is invalid in the select list» هم برایت روشن می‌شود. در این مقاله با یک جدول فروش همه‌چیز را قدم‌به‌قدم می‌بینیم.

جدول مثال

CREATE TABLE Sales (
  SaleId INT PRIMARY KEY,
  City   NVARCHAR(30),
  Product NVARCHAR(30),
  Amount INT
);

INSERT INTO Sales VALUES
 (1, N'Tehran',  N'Laptop', 900), (2, N'Tehran', N'Mouse', 20),
 (3, N'Tehran',  N'Laptop', 950), (4, N'Shiraz', N'Laptop', 880),
 (5, N'Shiraz',  N'Mouse',  25),  (6, N'Isfahan', N'Mouse', 22);

ترتیب منطقی اجرای SELECT

نوشتن دستور از SELECT شروع می‌شود، اما SQL Server آن را با ترتیب دیگری ارزیابی می‌کند:

به همین دلیل Alias تعریف‌شده در SELECT را نمی‌توان در WHERE یا HAVING استفاده کرد، اما در ORDER BY می‌توان.

GROUP BY چه می‌کند؟

GROUP BY ردیف‌هایی را که مقدار ستون (ستون‌ها)ی مشخصی دارند در یک گروه جمع می‌کند و برای هر گروه یک ردیف می‌دهد:

SELECT City, COUNT(*) AS SaleCount, SUM(Amount) AS Total
FROM Sales
GROUP BY City;

WHERE: فیلتر قبل از گروه‌بندی

فقط فروش لپ‌تاپ را می‌خواهیم، سپس بر اساس شهر گروه‌بندی کنیم:

SELECT City, SUM(Amount) AS Total
FROM Sales
WHERE Product = N'Laptop'
GROUP BY City;

HAVING: فیلتر بعد از گروه‌بندی

حالا فقط شهرهایی که جمع فروششان بیشتر از ۱۰۰۰ است:

SELECT City, SUM(Amount) AS Total
FROM Sales
GROUP BY City
HAVING SUM(Amount) > 1000;

نمی‌توان نوشت WHERE SUM(Amount) > 1000؛ در مرحله‌ی WHERE هنوز مجموعی ساخته نشده و SQL Server خطا می‌دهد.

ترکیب WHERE و HAVING

هر دو را می‌توان با هم به‌کار برد: اول ردیف‌ها فیلتر می‌شوند، بعد گروه‌ها.

SELECT City, COUNT(*) AS Cnt, AVG(Amount) AS AvgAmount
FROM Sales
WHERE Amount >= 20            -- ردیف‌های ارزان‌تر از ۲۰ حذف می‌شوند
GROUP BY City
HAVING COUNT(*) >= 2          -- فقط شهرهای با حداقل ۲ فروش
ORDER BY AvgAmount DESC;

خطای رایج: Column is invalid in the select list

اگر ستونی در SELECT بیاید که نه در GROUP BY است و نه داخل تابع تجمیعی، SQL Server این خطا را می‌دهد:

SELECT City, Product, SUM(Amount)   -- Product نه در GROUP BY است نه تجمیعی
FROM Sales
GROUP BY City;

-- Msg 8120: Column 'Sales.Product' is invalid in the select list
-- because it is not contained in either an aggregate function
-- or the GROUP BY clause.

دلیل: در هر گروه شهر، چند محصول مختلف داریم و SQL Server نمی‌داند کدام را نشان دهد. راه‌حل: یا Product را به GROUP BY اضافه کن، یا آن را در تابعی مثل MAX بگذار، یا از SELECT حذف کن:

SELECT City, Product, SUM(Amount) AS Total
FROM Sales
GROUP BY City, Product;

COUNT(*) یا COUNT(ستون)؟

• COUNT(*) تعداد همه‌ی ردیف‌های گروه را می‌شمارد.
• COUNT(Column) فقط ردیف‌هایی را می‌شمارد که آن ستون NULL نیست.
• COUNT(DISTINCT Column) مقدارهای متفاوت را می‌شمارد.

جمع‌بندی

برای تمرین بیشتر، درس‌های GROUP BY و HAVING را در Playground حل کن.