در این فصل توابع گروهی و روش های استفاده از آنها معرفی می شوند. این توابع به ازای هر مجموعه از سطرهای جدول یک نتیجه خاص برمی گردانند. همچنین  عبارت های Group By ، Having ، Order by و روش استفاده از آنها به همراه مثال توضیح داده می شوند.

توابع گروهی

توابع گروهی بعد از کلمه کلیدی SELECT استفاده می شوند. در یک دستور SQL می توان از چندین تابع گروهی استفاده نمود و آنها را با علامت ویرگول از هم جدا کرد.

نکته: زمانی که در یک تابع گروهی از کلمه کلیدی DISTINCT استفاده شود فقط سطرهای با مقدار غیر تکراری در نظر گرفته می شود. ولی دیتابیس اوراکل به صورت پیش فرض از ALL بجای DISTINCT استفاده می کند که در این حالت تمام سطرهای جدول در نظر گرفته می شوند.

نکته: در توابع گروهی نوع داده برای ورودی expr می تواند کاراکتر، عدد یا تاریخ باشد.

نکته: توابع گروهی به ازای مقدارهای NULL در سطرهای جدول هیچ نتیجه ای برنمی گردانند ولی می توان جهت جایگزینی مقادیر NULL از تابع های NVL،  NVL2 و … استفاده کرد.

**تابع AVG

AVG([DISTINCT|ALL]n)

این تابع از مقدارهای ستون n میانگین می گیرد.

**تابع SUM

SUM([DISTINCT|ALL]n)

این تابع مجموع مقادیر ستون n از جدول مورد نظر را محاسبه می کند.

نکته: دو تابع SUM و AVG برای داده های از نوع عدد استفاده می شوند.

**تابع MIN

MIN([DISTINCT|ALL]expr)

حداقل مقدار موجود در ستون expr را برمی گرداند.

**تابع MAX

MAX([DISTINCT|ALL]expr)

حداکثر مقدار در ستون expr را برمی گرداند.

نکته: از توابع MIN و MAX برای ستون های با نوع داده کاراکتری، عدد یا تاریخ استفاده می شود.

مثال: تعیین میانگین، مجموع، حداکثر و حداقل حقوق دریافتی کارمندانی که JOB_ID آنها مشابه REP می باشد.

 مثال: تاریخ استخدام اولین و آخرین کارمند سازمان.

**تابع COUNT

(COUNT([DISTINCT|ALL] expr

از این تابع برای شمارش استفاده می شود. اگر از علامت * بجای expr استفاده شود تعداد کل سطرهای متناظر با آن دستور باز می گردد حتی اگر  تعدادی از آنها NULL باشند. ولی می توان بجای expr از نام یکی از ستون ها استفاده نمود که در این حالت تعداد سطرهای غیر NULL آن ستون را بازمی گرداند. 

نکته: خروجی (‘COUNT(1)  ، COUNT(‘sds یا هر عدد و رشته دیگر در داخل تابع COUNT برابر با خروجی (*)COUNT  است.

مثال: چند سطر از جدول employees دارای شماره دپارتمان 50 است.

مثال: چند نفر در دپارتمان شماره 50 حق کمیسیون دریافت کرده اند(مقدار غیر NULL).

نکته: اگر در تابع COUNT از کلمه DISTINCT استفاده شود فقط تعداد مقادیر غیر تکراری شمرده می شوند.

مثال: نمایش تعداد دپارتمان های سازمان(به صورت غیر تکراری)

نکته: تمام توابع گروهی سطرهایی که مقادیر NULL دارند را رد می کنند و درنظر نمی گیرند.

مثال: در قسمت اول میانگین مقادیر ستون حق کمیسون محاسبه می گردد در حالی که سطرهای NULL در عملیات محاسبه میانگین قرار ندارند. ولی در قسمت دوم با استفاده از تابع NVL تمام سطرهای NULL به مقدار 0 تبدیل شده و سپس میانگین تمام سطرها محاسبه شده است.

عبارت GROUP BY

در دستورات SQL با استفاده از GROUP BY اطلاعات یک جدول را به چند دسته مختلف تقسیم می کنیم. این دسته بندی بر اساس ستون یا ستون هایی که نام آنها بعد از عبارت GROUP BY بیان می شود انجام می گیرد.

نکته: در عبارت GROUP BY نمی توان از نام مستعار برای ستون ها استفاده نمود.

نکته: در دستورات SQL نام تمام ستون هایی که بعد از کلمه کلیدی SELECT استفاده می شوند باید در عبارت GROUP BY نیز بکار رود مگر آنکه نام یک ستون در یک تابع گروهی استفاده شده باشد.

مثال: نام ستون DEPARTMENT_ID که بعد از کلمه کلیدی SELECT استفاده شده در عبارت GROUP BY نیز بکار رفته است. همچنین در این دستور هر کدام از دپارتمان ها به عنوان یک گروه مجزا در نظر گرفته می شوند و سپس میانگین حقوق در هر دپارتمان محاسبه می گردد.

عبارت ORDER BY

با استفاده از عبارت ORDER BY می توان سطرهای انتخاب شده را به صورت صعودی (ASC)و یا نزولی (DESC) نمایش داد. عبارت ORDER BY بعد از عبارت WHERE استفاده می شود.

مثال: اطلاعات کارمندان بر اساس ترتیب تاریخ استخدام مرتب شده و نمایش می یابد.

نکته: اگر در عبارت ORDER BY ترتیب صعودی یا نزولی مشخص نگردد به صورت پیش فرض نمایش اطلاعات به صورت صعودی یا ASC خواهد بود.

نکته: اگر بعد از عبارت ORDER BY چندین ستون ذکر شود، ابتدا مرتب سازی بر اساس ستون اول انجام می گیرد و سپس اگر مقادیر ستون اول برای سطرها یکسان بود از ستون بعدی جهت مرتب سازی استفاده می گردد. مثال:

نکته: برخلاف عبارت WHERE در عبارت ORDER BY می توان از نام مستعار  (ALIAS) یک ستون نیز استفاده نمود. مثال:

+ هر کدام از مقادیر عددی، تاریخ یا کاراکتری می توانند توسط عبارت ORDER BY مرتب شوند.

+ اگر ORDER BY به صورت صعودی باشد مقادیر NULL در انتهای سطرها نمایش می یابند و اگر نزولی باشد در ابتدای سطرها.

+ می توان عبارت ORDER BY را براساس یک ستون که در کل دستور select به کار نرفته است نیز استفاده کرد.

+ می توان بعد از عبارت ORDER BY یک عدد عنوان کرد که منظور از آن شماره ستون انتخاب شده در دستور SELECT خواهد بود.

مثال: ستون DEPARTMENT_ID به عنوان سومین ستون در دستور SELECT انتخاب شده است بنابراین منظور از شماره 3 بعد از عبارت  ORDER BY ستون DEPARTMENT_ID است..

+ در اوراکل نگارش 12 به بعد می توان در دستور SELECT و بعد از عبارت ORDER BY از عباراتی جهت تعیین حداکثر تعداد سطرهایی که می خواهیم نمایش یابند استفاده نمود. همچنین می توان شماره سطری که می خواهیم نمایش اطلاعات از آن محل شروع شود نیز مشخص کرد.(OFFSET و FETCH)

مثال:

+ اگر بعد ار عبارت GROUP BY از عبارت ORDER BY استفاده شود نتایج به صورت صعودی و یا نزولی نمایش می یابند.

مثال:

SELECT   department_id, AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary);

مثال:

SELECT department, MAX(salary) AS “Highest salary” FROM employees

GROUP BY department order by salary desc;

+ اگر در یک دستور SQL نام یک ستون در عبارت GROUP BY استفاده گردد لزومی ندارد بعد از کلیدی SELECT انتخاب شود.

+ می توان از چندین ستون در عبارت GROUP BY استفاده نمود و جدول را بر اساس آن ستون ها دسته بندی نمود.

مثال: مطابق این دستور ابتدا جدول EMPLOYEE بر اساس شماره دپارتمان دسته بندی می شود و سپس در هر دپارتمان افرادی که JOB_ID یکسان دارند در یک گروه قرار می گیرند.

SELECT   department_id, job_id, sum(salary) FROM     employees

GROUP BY department_id, job_id ORDER BY job_id;

مثال: استفاده از چند ستون در GROUP BY و استفاده از عبارت ORDER BY.

+ اگر در یک دستور SQL بعد از کلمه کلیدی SELECT یک تابع گروهی و نام یک ستون مثلا expr بکار رفته باشد باید نام ستون expr در یک عبارت GROUP BY استفاده شود وگرنه آن دستور اجرا نمی شود و از طرف اوراکل خطا دریافت می کنیم.

مثال: در دستور اول نام ستون DEPARTMENT_ID و در دستور دوم ستون JOB_ID در عبارت GROUP BY استفاده نشده است.

+ نمی توان از توابع گروهی در قسمت WHERE یک دستور SELECT استفاده نمود.

مثال: نمایش میانگین حقوق دپارتمان هایی که از 8000 بیشتر هستند. بدلیل استفاده از تابع گروهی در قسمت WHERE خطای اوراکل دریافت می کنیم.

عبارت HAVING

وقتی از عبارت GROUP BY جهت گروه بندی یک جدول استفاده می شود می توان این گروه ها را با عبارت HAVING محدود نمود. بنابراین مفهوم عبارت HAVING و WHERE یکسان است ولی کاربرد متفاوت دارند. عبارت WHERE سطرهای جدول را محدود می کند ولی عبارت HAVING به منظور محدودسازی گروه های جدول استفاده می شود.

نکته: فقط زمانی که جدول با استفاده از عبارت GROUP BY دسته بندی شده است می توان از HAVING استفاده نمود.

+ دیتابیس اوراکل در زمان مشاهده یک دستور با عبارت HAVING به ترتیب این 3 مرحله را اجرا می کند:

—-سطرهای جدول با توجه به عبارت GROUP BY گروه بندی می شوند.

—-اگر یک تابع گروهی استفاده شده باشد در گروه ها اعمال می شود.

—-گروه هایی که مطابق عبارت HAVING محدود شده اند نمایش می یابند.

مثال: گروه هایی که حداکثر حقوق دریافتی در آنها بیشتر از 10000 واحد می باشد نمایش می یابند

مثال: همزمان از عبارت WHERE جهت محدودسازی سطرها و از عبارت HAVING جهت محدودسازی گروه ها استفاده شده است.

توابع گروهی تودرتو

می توان در دستورات SQL از توابع گروهی به صورت تودرتو ( حداکثر 2 مورد تابع) استفاده نمود. در این حالت استفاده از عبارت GROUP BY  اجباری می باشد.

مثال: نمایش حداکثر میانگین حقوق در بین تمام دپارتمان ها.