توابع تجمیعی، GROUP BY و HAVING در SQL اکسس

رکوردهای جزئی سفارش برای حسابرسی لازم‌اند، اما مدیر معمولاً مجموع فروش، تعداد سفارش، میانگین و رتبه‌بندی می‌خواهد. توابع تجمیعی در SQL اکسس سطرهای تراکنش را به خلاصه‌ای فشرده برای گزارش، داشبورد، نمودار و کد VBA تبدیل می‌کنند.

در این درس تفاوت فیلتر سطر با فیلتر گروه، دلیل حضور تمام فیلدهای غیرتجمیعی در GROUP BY و روش ساخت کوئری خلاصه پارامتری بررسی می‌شود تا با افزایش قواعد کسب‌وکار، SQL همچنان خوانا باقی بماند.

مثال‌ها بر داده فروش تکیه دارند، اما همین الگو برای گردش موجودی، حضور و غیاب، تیکت پشتیبانی، ساعت پروژه و هر جدولی که باید بر اساس مشتری، ماه، دسته یا کارمند خلاصه شود قابل استفاده است.

جایگاه این درس در مسیر آموزش SQL اکسس

این مبحث پس از SELECT، WHERE، JOIN و ویرایش داده قرار می‌گیرد، زیرا تجمیع به نتیجه جزئیات صحیح وابسته است. اگر join رکورد تکراری بسازد یا فیلتر اشتباه باشد، مجموع نهایی نیز همان خطا را بزرگ‌تر می‌کند.

کوئری را در دو سطح ببینید: سطح جزئیات تعیین می‌کند چه سطرهایی وارد محاسبه شوند و سطح گروه مشخص می‌کند سطرها چگونه جمع شوند و کدام گروه باقی بماند. این تفکیک مانع استفاده بی‌دلیل از HAVING برای تمام شرط‌ها می‌شود.

پیش‌نیازها و مدل داده نمونه

در مثال‌ها از جدول‌های Customers، Orders، OrderDetails، Products و Categories استفاده می‌شود. CustomerID کلید اصلی مشتری، OrderID کلید اصلی سفارش و ProductID کلید اصلی محصول است. فیلدهای کلید خارجی باید با کلید اصلی متناظر نوع داده سازگار داشته باشند و در پایگاه‌های بزرگ برای join و فیلترهای پرتکرار ایندکس شوند.

پیش از اجرای مثال‌ها از فایل اکسس نسخه پشتیبان تهیه کنید و نام جدول‌ها و فیلدها را با پایگاه خود تطبیق دهید. اگر نامی فاصله دارد آن را داخل کروشه قرار دهید. ابتدا یک SELECT ساده با تعداد رکورد محدود بسازید و سپس clauseها، پارامترها یا عبارت‌های پیشرفته را مرحله‌به‌مرحله اضافه کنید.

ساختار اصلی و مدل ذهنی

کوئری تجمیعی چند سطر جزئیات را به یک سطر برای هر گروه تبدیل می‌کند. WHERE رکوردهای ورودی را پیش از گروه‌بندی محدود می‌کند، GROUP BY کلید گروه را تعیین می‌کند، توابع Sum و Count و Avg مقدار خلاصه را می‌سازند و HAVING گروه‌های نهایی را فیلتر می‌کند.

SELECT CustomerID,
       Count(*) AS OrderCount,
       Sum(TotalAmount) AS TotalSales,
       Avg(TotalAmount) AS AverageOrder
FROM Orders
WHERE OrderDate Between #2026-01-01# And #2026-12-31#
GROUP BY CustomerID
HAVING Sum(TotalAmount) >= 1000
ORDER BY Sum(TotalAmount) DESC;

دستور را بر اساس جریان منطقی داده تحلیل کنید: ابتدا سطرهای منبع مشخص می‌شوند، سپس فیلتر سطح رکورد اجرا می‌شود، داده در صورت نیاز ترکیب یا گروه‌بندی می‌شود، ستون‌های خروجی محاسبه می‌شوند، شرط سطح نتیجه اعمال می‌شود و در پایان مرتب‌سازی صورت می‌گیرد. این مدل نشان می‌دهد چرا جابه‌جایی یک شرط می‌تواند نتیجه را تغییر دهد.

کاربردهای مناسب

• فروش ماهانه بر اساس مشتری یا دسته محصول
• شمارش تیکت باز برای هر کارشناس
• محاسبه میانگین زمان تحویل بر اساس شرکت حمل
• یافتن گروه‌هایی که مجموع آن‌ها از حد مدیریت بیشتر است
• آماده‌سازی داده خلاصه برای نمودار و گزارش

نمونه کاربردی با قواعد واقعی

گزارش کاربردی باید سفارش لغوشده را کنار بگذارد، بازه تاریخ دریافت کند، داده را بر اساس مشتری گروه‌بندی کند و فقط مشتریانی را نشان دهد که مجموع فروش آن‌ها از حد تعیین‌شده بیشتر است.

PARAMETERS [pStartDate] DateTime,
           [pEndDate] DateTime,
           [pMinimumSales] Currency;
SELECT C.CustomerID,
       C.CustomerName,
       Count(O.OrderID) AS OrderCount,
       Sum(O.TotalAmount) AS TotalSales,
       Avg(O.TotalAmount) AS AverageOrderValue
FROM Customers AS C
INNER JOIN Orders AS O
    ON C.CustomerID = O.CustomerID
WHERE O.OrderDate Between [pStartDate] And [pEndDate]
  AND O.Status <> 'Cancelled'
GROUP BY C.CustomerID, C.CustomerName
HAVING Sum(O.TotalAmount) >= [pMinimumSales]
ORDER BY Sum(O.TotalAmount) DESC;

CustomerID و CustomerName هر دو در GROUP BY قرار گرفته‌اند، چون بدون تابع تجمیعی در SELECT دیده می‌شوند. شرط مجموع فروش در HAVING است و تاریخ و وضعیت در WHERE قرار می‌گیرند، زیرا پیش از تشکیل گروه‌ها روی سطرهای سفارش اثر دارند.

اجرای کوئری از VBA

برای اتوماسیون قابل نگهداری، SQL را به‌صورت یک QueryDef نام‌دار ذخیره و از DAO اجرا کنید. پارامتر صریح از اتصال متن به رشته SQL بهتر است، زیرا Access نوع داده را پیش از اجرا می‌شناسد. روال زیر الگوی عمومی را نشان می‌دهد؛ نام QueryDef و پارامترها را با کوئری ذخیره‌شده همین درس هماهنگ کنید.

Option Explicit

Public Sub ShowCustomerSalesSummary()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    On Error GoTo CleanFail

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryCustomerSalesSummary")
    qdf.Parameters("pStartDate") = DateSerial(2026, 1, 1)
    qdf.Parameters("pEndDate") = DateSerial(2026, 12, 31)
    qdf.Parameters("pMinimumSales") = 1000@

    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Do While Not rs.EOF
        Debug.Print rs!CustomerName, rs!OrderCount, rs!TotalSales
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

در این نمونه Recordset از نوع snapshot برای خواندن نتیجه باز می‌شود. برای INSERT، UPDATE یا DELETE باید از روش اجرای action query استفاده و RecordsAffected را کنترل کنید. بخش پاک‌سازی نیز باید اشیا را در حالت موفق و خطا ببندد تا اتصال یا Recordset باز باقی نماند.

روش کنترل نتیجه

1. کوئری منبع را بدون clause پیشرفته اجرا و تعداد سطرهای اولیه را ثبت کنید.
2. یک حالت کوچک را دستی محاسبه و با خروجی SQL مقایسه کنید.
3. پارامتری وارد کنید که نتیجه خالی بدهد و رفتار کد فراخوان را بررسی کنید.
4. یک مقدار Null یا رکورد تکراری آزمایشی اضافه و انطباق نتیجه با قاعده مستند را کنترل کنید.
5. تعداد رکوردهای QueryDef ذخیره‌شده را با Recordset بازشده در VBA مقایسه کنید.
6. نام و نوع ستون‌های نهایی مورد نیاز گزارش یا export را ثبت کنید.

خطاهای رایج و روش اصلاح

کارایی، ایمنی و نگهداری

برای اجرای مطمئن توابع تجمیعی در SQL اکسس بهتر است ابتدا یک نسخه آزمایشی از پایگاه داده بسازید و چند رکورد با مقادیر قابل تشخیص در آن قرار دهید. کوئری را پیش از اتصال به فرم، گزارش یا کد VBA مستقیماً در محیط Query Design اجرا کنید. این روش خطای طراحی SQL را از خطای اتوماسیون جدا می‌کند و بررسی نتیجه را ساده‌تر می‌سازد.

زبان SQL اکسس به SQL استاندارد نزدیک است، اما در نشانه‌گذاری تاریخ، wildcardها، پارامترها و موتور عبارت‌ها تفاوت‌هایی دارد. هنگام کار با توابع تجمیعی در SQL اکسس ابتدا دستور را در خود Access آزمایش کنید. پس از تأیید نتیجه، همان منطق را به QueryDef ذخیره‌شده یا رشته SQL در VBA منتقل کنید تا خطاهای نقل‌قول و نوع پارامتر زودتر آشکار شوند.

برای فیلدهای محاسباتی alias روشن انتخاب کنید. نام مستعار باید معنای خروجی را نشان دهد، نه اینکه فقط عبارت را تکرار کند. alias مناسب اتصال گزارش، خواندن Recordset در VBA و نگهداری پروژه را آسان می‌کند. اگر نام فیلد فاصله دارد یا با واژه رزروشده تداخل پیدا می‌کند، آن را داخل کروشه قرار دهید.

رفتار مقدار Null را از ابتدا مشخص کنید. توابع تجمیعی، شرط‌ها، joinها و عبارت‌های محاسباتی همگی Null را یکسان مدیریت نمی‌کنند. ابتدا تعیین کنید Null در منطق کسب‌وکار به معنی مقدار ناشناخته، نامرتبط یا صفر است. تابع Nz را فقط زمانی به‌کار ببرید که جایگزینی Null با مقدار پیش‌فرض واقعاً درست باشد.

اعتبارسنجی ورودی را از اجرای کوئری جدا کنید. تاریخ‌ها، شناسه‌ها و حدود عددی باید پیش از تخصیص به پارامتر بررسی شوند. متن واردشده توسط کاربر را در صورت وجود پارامتر به SQL نچسبانید. پارامترها علاوه بر کاهش خطاهای قالب تاریخ و جداکننده اعشاری، مشکل apostrophe در متن را نیز کنترل می‌کنند.

کارایی را با داده‌ای نزدیک به حجم واقعی اندازه بگیرید. کوئری‌ای که روی بیست رکورد سریع است ممکن است روی صدها هزار رکورد کند شود. فیلدهای پرتکرار در join و فیلتر انتخابی را ایندکس کنید، روی فیلد ایندکس‌شده تابع غیرضروری اعمال نکنید و فقط ستون‌هایی را برگردانید که مرحله بعد واقعاً لازم دارد.

برنامه آزمون باید داده معمولی، مقدار مرزی، Null، رکورد تکراری و نتیجه خالی را پوشش دهد. بررسی کنید اگر هیچ رکوردی پیدا نشد، اگر یک گروه فقط یک عضو داشت یا اگر دو سطر مقدار مرتب‌سازی یکسان داشتند، خروجی همچنان قابل پیش‌بینی باشد. نتیجه مورد انتظار را پیش از بازنویسی کوئری ثبت کنید.

کوئری ذخیره‌شده بخشی از مستندات پروژه است. برای QueryDef نام توصیفی انتخاب کنید، در ماژول VBA یک توضیح کوتاه درباره ورودی و خروجی بنویسید و هر کوئری را بر یک مسئولیت متمرکز نگه دارید. چنین کوئری‌ای مستقیماً قابل آزمایش است و می‌تواند میان فرم، گزارش و کد مشترک باشد.

اگر خروجی کوئری برای گزارش مهم یا عملیات تغییر داده استفاده می‌شود، پارامترها و تعداد رکوردهای برگردانده‌شده را ثبت کنید. لازم نیست داده حساس در log ذخیره شود. زمان اجرا، نام کوئری، خلاصه پارامترها و تعداد سطرها معمولاً برای عیب‌یابی کافی است.

توابع تجمیعی در SQL اکسس را بخشی از یک زنجیره پردازش داده ببینید. ورودی، شکل خروجی و مصرف‌کننده نتیجه باید روشن باشد. اینکه خروجی برای نمودار، گزارش، export به Excel یا مرحله ویرایش داده استفاده می‌شود، روی aliasها، ترتیب، سیاست Null و میزان جزئیات اثر مستقیم دارد.

تمرین مرحله‌به‌مرحله

تمرین را در سه نسخه بسازید. در نسخه اول فقط سطرهای خام را با join و فیلتر ضروری برگردانید. در نسخه دوم تکنیک اصلی این درس را اضافه و نتیجه را با داده کم کنترل کنید. در نسخه سوم پارامتر، alias، مرتب‌سازی و روال VBA را بیفزایید. هر نسخه را موقتاً با نام جدا ذخیره کنید تا علت هر تغییر در خروجی مشخص باشد.

در تمرین دوم فقط یک قاعده کسب‌وکار را تغییر دهید؛ برای نمونه بازه گزارش، حذف سفارش‌های لغوشده، انتخاب دسته متفاوت یا تغییر خروجی از جزئیات به خلاصه. کوئری مناسب باید اجازه دهد این تغییر در یک پارامتر، شرط یا عبارت محاسباتی انجام شود و به بازنویسی چند بخش نامرتبط نیاز نداشته باشد.

در پایان کوئری را از دید توسعه‌دهنده دیگری بازبینی کنید. آیا بدون بازکردن فرم‌ها می‌توان جدول‌های ورودی، پارامترها، ستون‌های خروجی و سطح هر سطر را تشخیص داد؟ aliasهای مبهم را اصلاح، ستون‌های بلااستفاده را حذف و در ماژول VBA یک توضیح کوتاه درباره فرض‌ها ثبت کنید.

درس‌های مرتبط این مجموعه

این درس پس از مباحث SELECT، WHERE، JOIN و ویرایش داده قرار می‌گیرد. برای مرور عملیات تغییر رکورد می‌توانید مقاله ویرایش و حذف داده‌ها در SQL اکسس با VBA را بخوانید. پیشنهادهای لینک داخلی، درس‌های بعدی این مجموعه را نیز تا زمان انتشار با وضعیت planned نگه می‌دارند.

جمع‌بندی

پس از این درس می‌توانید totals query بسازید، تابع تجمیعی مناسب را انتخاب کنید، شرط را در WHERE یا HAVING قرار دهید، پارامترها را صریح تعریف کنید و نتیجه را با VBA بخوانید.

روش قابل اعتماد، ساخت تدریجی است: SQL را با داده کنترل‌شده ثابت کنید، نوع پارامترها را صریح بنویسید، رفتار Null و داده تکراری را بیازمایید و سپس اتوماسیون را اضافه کنید. با این ترتیب، تغییر جدول‌ها، فرم‌ها یا نیاز گزارش‌گیری هزینه کمتری خواهد داشت.