در این مطلب از مجله فرادرس، به بررسی دستور Group by در SQL پرداخته‌ایم. زبان SQL یا «Structured Query Language»، زبان کوئری‌نویسی بسیار مشهور و پرکاربردی است که به‌طور وسیعی برای دسترسی و انجام عملیات بر روی داده‌های ذخیره شده استفاده می‌شود. دستور Group by در SQL برای گروه‌بندی مقادیر شبیه به هم بکار می‌رود. در ابتدا باید به بررسی عبارت بپردازیم، کار عبارت Group by چیست و چرا نیاز داریم از این عبارت در کوئری‌های SQL استفاده کنیم. سپس چند مورد از موارد کاربرد این عبارت را همراه با سایر عبارت‌های SQL به‌صورت جداگانه خواهید دید و برای هر کدام مثال می‌زنیم.

عبارت GROUP BY

برای گروه‌بندی مقادیر یکسان بکار می‌رود و در نهایت هم خلاصه‌ای از نتیجه را برمی‌گرداند. اگر ستونی که در عبارت GROUP BY

استفاده شده است، مقادیر خالی داشته باشد یعنی در ردیفی مقدار NULL قرار گرفته باشد، توسط عبارت GROUP BY

به عنوان مقادیر همسان شناخته می‌شوند در نتیجه همه NULL-ها را در یک گروه دسته‌بندی می‌کند.

روش پیاده سازی دستور Group by در SQL

از این عبارت، درون عبارت دستوری SELECT

 استفاده می‌کنیم و بعد از عبارت WHERE

 قرار می‌دهیم. اگر بخواهید در همان کوئری از عبارت ORDER BY

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

همچنین در زمان کار کردن با پایگاه داده بعضی وقت‌ها لازم می‌شود که توابعی را با هم در یک کوئری تجمیع کنیم. بعضی اوقات باید توابعی نظیر COUNT()

 , MIN()

 , MAX()

 , SUM()

 و AVG()

 را در کنار نام ستون‌ها با یکدیگر تجمیع کنیم تا بتوانیم خروجی مورد نظرمان را پیدا کنیم.

به هرحال در این مطلب سعی کرده‌ایم این موارد را به‌صورتی ساده و شفاف بیان کنیم. ولی برای شروع، مهم‌ترین کار این است که در ابتدا ببینیم سینتکس پایه دستور Group by در SQL به چه‌ صورت است. این سینتکس را در کد زیر پیاده‌سازی کرده‌ایم.

1  SELECT ColumnName, FunctionName
2  FROM TableName
3  WHERE Condition
4  GROUP BY ColumnName

در ادامه کد کوئری که در بالا مشاهده می‌کنید را به‌صورت جزءبه‌جزء توضیح داده‌ایم.

• ColumnName

   نام ستون‌هایی است که می‌خواهید در خروجی نمایش دهید یا در دستور GROUP BY

   مشخص کرده‌اید تا مقدارهای همسان را از آن ستون‌ها دسته‌بندی کنید.

• FunctionName 

   نام «توابع تجمعی» (Aggregate Functions) مانند COUNT()

   , MIN()

   , MAX()

   , SUM()

   و AVG()

   را نشان می‌دهد که می‌توان در SQL استفاده کرد.

• TableName

   نام جدولی است که می‌خواهید از آن جدول، مجوعه‌ای از نتایج را استخراج کنید و در خروجی نمایش دهید. در واقع جدولی که عملیات قرار است روی داده‌های آن جدول اجرا شود.

• Condition

   عبارتی است که در بند مربوط به WHERE

   استفاده می‌شود. این عبارت برای فیلتر کردن داده‌های خروجی بکار می‌رود.

تابع تجمعی چیست؟

در SQL، «تابع تجمعی» (Aggregate Function) مانند ابزاری است که روی داده‌های بسیار زیادی عملیات ریاضی انجام می‌دهد و نتیجه‌ای یکتا برمی‌گرداند. این ابزارها شامل توابعی مانند COUNT()

 , MIN()

 , MAX()

 , SUM()

 و AVG()

 و غیره هستند. تابع‌های تجمعی به مقادیر «خالی» (NULL) توجه نمی‌کنند، به جز وقتی که تعداد را محاسبه می‌کنید.

یعنی اینکه این توابع در هنگام انجام محاسبات، مقادیر NULL را نادیده می‌گیرند و به جای آن‌ها تنها با مقادیر غیرخالی محاسبات انجام می‌دهند. به عنوان مثال، تابع AVG()

 میانگین مقدارهای عددی را محاسبه می‌کند و مقدار محاسبه شده را برمی‌گرداند. تابع COUNT()

 تعداد کل رکوردهایی را که شمرده برمی‌گرداند و توجهی به مقادیر NULL نمی‌کند.

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

در بخش بعدی نحوه استفاده از دستور Group by در SQL را با استفاده از چند مثال کاربردی نمایش خواهیم داد.

کاربرد دستور Group by در SQL

در این بخش موارد متنوعی از کاربرد دستور Group by در SQL را توضیح خواهیم داد. از جدولی به نام Resources استفاده خواهیم کرد تا همه این کاربردهای مختلف را بررسی کنیم. این جدول جزییات مربوط به کارمندان شرکت فرضی را ذخیره کرده است. این اطلاعات شامل موارد فهرست شده در زیر می‌شود.

• «نام» (Name)
• «شماره شناسایی» (Id)
• «شهر» (City)
• «سِمَت در شرکت» (Designation)
• «حقوق» (Salary)
• «سن» (Age)

نشان خواهیم داد که چگونه بند مربوط به دستور Group by در SQL را در کنار چندین عبارت دیگر بکار بگیرید تا نتیجه دلخواه خود را بدست آورید.

به وسیله اجرا کردن کد زیر محتویات جدول را خواهیم دید تا وضعیت داده‌ها را بررسی کنیم و نتایج خروجی از اجرای هر کوئری اعتبار سنجی شوند.

1  SELECT *
2    FROM [DemoDB].[dbo].[Resources]

تصویری از خروجی که به وسیله کوئری ایجاد شده است را در پایین می‌بینید.

از این به بعد کدها و خروجی آن‌ها را درمحیط گرافیکی هم نمایش خواهیم داد.

مطلب خود را با ابتدایی‌ترین روش کار عبارت group by ادامه می‌دهیم.

کاربرد ساده و ابتدایی دستور Group by در SQL

در این بخش کمک می‌کنیم با ابتدایی‌ترین کاربرد دستور group by در SQL آشنا شوید. اینجا مجموع کارمندان را برحسب وضعیت استخدام آن‌ها برمی‌گردانیم. دو مقدار متفاوت برای وضعیت استخدام کارمندان وجود دارد، یک نوع کارمند دائمی و دیگری کارمند قراردادی است.

کوئری زیر را اجرا خواهیم کرد. در این کوئری از ستون وضعیت استخدام EmpNature

 و تابع تجمیع شده COUNT()

 در عبارت SELECT

 استفاده کرده‌ایم. البته در آخر بند GROUP BY

 را هم بعد از عبارت FROM

 روی ستون EmpNature

 به کوئری اضافه می‌کنیم تا مقادیر شبیه به هم را دسته‌بندی کند.

1  SELECT EmpNature, COUNT(*) AS [No. of Resources]
2   FROM [DemoDB].[dbo].[Resources]
3   GROUP BY EmpNature

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

بعد از مطلب بالا به اجرای بند group by روی چند ستون مختلف در کوئری خواهیم پرداخت. از مثال قبلی به عنوان مرجع استفاده خواهیم کرد تا ستون‌های بیشتری را به همان کوئری اضافه کنیم. در اینجا به عبارت SELECT

 و GROUP BY

 ستون شهر City

 را نیز اضافه می‌کنیم تا تعداد کارمندان را برحسب وضعیت استخدام و شهر آن‌ها نمایش دهیم.

1  SELECT EmpNature, City, COUNT(*) AS [No. of Resources]
2   FROM [DemoDB].[dbo].[Resources] 
3   GROUP BY EmpNature, City

خروجی کوئری بالا در تصویر زیر نمایش داده شده است. در اینجا می‌بینید که عبارت GROUP BY

 همه کارمندان را برحسب شهر و وضعیت استخدام شغلشان دسته‌بندی کرده است. می‌توانیم ببینیم که ۳ کارمند در شهر بمبئی به صورت دائمی کار می‌کنند در حالی که در شهر لندن یک کارمند دائمی و یک کارمند قراردادی وجود دارد.

همچنین می‌توانیم از چندین تابع همراه با این عبارت استفاده کنیم. در مثال پایین می‌بینید که از دو تابع «تجمعی» (Aggregate) COUNT()

 و AVG()

 استفاده کرده‌ایم. کوئری پایین مجموع تعداد کارمندان و میانگین سن‌ کارمندان را بر اساس نوع قراردادهای کاری آن‌ها برمی‌گرداند.

1  SELECT EmpNature, COUNT(*) AS [No. of Resources], AVG(Age) AS [Average Age]
2   FROM [DemoDB].[dbo].[Resources]    
3   GROUP BY EmpNature

خروجی این کوئری نشان می‌دهد که ۲ کارمند قراردادی در حال کار هستند و میانگین سنی ۲۵ سال دارند و همچنین میانگین سنی همه کارمندان دائمی که ۴ نفر هستند، ۲۷ سال است.

کاربرد دستور Group by در SQL همراه با عبارت WHERE

در این بخش روش بکاربردن عبارت Group by را بعد از بند مربوط به WHERE

 بررسی خواهیم کرد. از عبارت WHERE

برای اعمال فیلتر بر روی خروجی مطلوب بر اساس شرایط مختلف می‌توان استفاده کرد. در واقع به کمک عبارت WHERE

برای داده‌های خروجی شرط تعیین می‌کنیم. وقتی که در عبارت دستوری SELECT

 از بند GROUP BY

 بعد از بند WHERE

استفاده می‌کنیم اول از همه کوئری، ردیف‌هایی را که با شرایط تعیین شده در عبارت WHERE

هم خوانی نداشته باشند از گزینه‌های مورد بررسی حذف می‌کند سپس عملیات دسته‌بندی طبق نظر عبارت GROUP BY

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

اولین مثالی که مورد استفاده قراردادیم را به عنوان مرجع کد بکار می‌بریم و شرطی را به کمک عبارت WHERE

بر روی آن اعمال می‌کنیم. می‌توانید نگاهی به کد دستوری SQL زیر بی‌اندازید که تعداد کل کارمندان را برحسب شهر بمبئی جدا کرده است و آن‌ها را براساس نوع موقعیت شغلی‌شان دسته‌بندی می‌کند.

1  SELECT EmpNature, COUNT(*) AS [No. of Resources]
2   FROM [DemoDB].[dbo].[Resources]  
3   WHERE City = 'Mumbai'
4   GROUP BY EmpNature

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

کاربرد دستور Group by در SQL همراه با عبارت ORDER BY

همراه با عبارت ORDER BY

 نیز می‌توان از بند GROUP BY

در کوئری SQL استفاده کرد. در این بخش نمایش خواهیم داد که چگونه در کنار عبارت ORDER BY

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

را باید بعد از بند مربوط به GROUP BY

بیاوریم.

از کوئری که در مثال اول نمایش داده شد، استفاده می‌کنیم و به‌طور دائم با افزودن بندهای مختلف کوئری‌های مختلف می‌سازیم. این دفعه عبارت ORDER BY

را به کوئری مرجع اضافه می‌کنیم.

1  SELECT EmpNature, COUNT(Name) AS [Resources]
2   FROM [DemoDB].[dbo].[Resources]  
3    GROUP BY EmpNature
4    ORDER BY COUNT(Name) DESC

می‌توانید خروجی را ببینید. این دفعه اولین ردیف به عنوان وضعیت استخدام دائمی و ردیف دوم به‌عنوان وضعیت استخدام قراردادی نمایش داده می‌شود. در جایی که عبارت ORDER BY

مشخص نشده بود، در مثال‌های بالا، وضعیت استخدام دائمی به صورت از آخر به اول، در ردیف دوم نمایش داده می‌شد.

کاربرد دستور Group by در SQL همراه با عبارت HAVING

در این بخش می‌آموزیم که چگونه از بند مربوط به Group by در کوئری همراه با عبارت HAVING استفاده کنیم. می‌توانید ۲ مجموعه از کوئری‌ها را ببینید.

• مجموعه اول همه شهرها را به همراه میانگین سنی کارمندان برمی‌گرداند.
• کوئری دوم، مورد استفاده‌ی عبارت HAVING را نمایش می‌دهد. به این صورت که همان شهرها را به عنوان خروجی برمی‌گرداند همراه با میانگین حقوق، در حالی که میانگین سن کارمندان کمتر از ۲۵ سال است.

1  SELECT City, AVG(Age) AS [AvgAge]
2   FROM [DemoDB].[dbo].[Resources] 
3   GROUP BY City
4   GO
5 
6 SELECT  City, AVG(Salary) AS [Avg Salary]
7   FROM [DemoDB].[dbo].[Resources]  
8    GROUP BY City
9    HAVING AVG(Age)<25

باید خروجی کوئری‌های بالا را به کمک تصویر زیر بررسی کنیم. اینجا به ترتیب برای هر کوئری دو مجموعه خروجی می‌توانیم ببینیم.

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

جمع بندی

در این مطلب از مجله فرادرس توضیح دادیم که چگونه از دستور Group by در SQL استفاده کنید تا خروجی مطلوب خود را بدست آورید.

از عبارت GROUP BY

 برای دسته‌بندی مقادیر شبیه به هم در ستون‌های مشخص شده، استفاده می‌شود تا بتوانیم خروجی مورد نظر را با دقت تمام بدست بیاوریم. موارد کاربردی متنوعی را نمایش دادیم. نحوه استفاده از دستور Group by

  در SQL

 را همراه با عبارت‌ WHERE

 ، بند ORDER BY

 در کوئری و عبارت HAVING

 با مثال‌های مختلف نمایش دادیم.

سوالات متداول

در ادامه به بررسی پر تکرارترین سوالاتی که توسط افراد مختلف درباره دستور Group by در SQL پرسیده شده است پرداخته‌ایم، زیرا شاید برای شما هم پیش بیایند.

چگونه داده های تکراری را در SQL حذف کنیم؟

با استفاده از کلمه کلیدی DISTINCT و دستور GROUP BY به همراه عبارت INNER JOIN می‌توانیم مقادیر تکراری موجود در جدول را حذف کنیم. فراموش نکنید که همیشه قبل از ایجاد تغییرات از داده‌ها بکاپ بگیرید.

چرا از دستور Group by در SQL استفاده می کنیم؟

به کمک دستور Group by در SQL و توابع تجمعی می‌توانیم انواع مختلف داده‌ها را دسته‌بندی کنیم و اطلاعات از جدول استخراج کنیم.

دستورهای GROUP BY و ORDER BY در SQL چه کاربردی دارند؟

عبارت GROUP BY داده‌ها را به وسیله دسته‌بندی کردن آن‌ها بر اساس ستون‌هایی که در کوئری مشخص شده، منظم می‌کند و با توابع Aggregate نیز قابل استفاده است. عبارت ORDER BY کمک می‌کند که مجموعه داده‌هایی را که از نتیجه کوئری بدست آورده‌اید بر اساس الفبا یا عدد به‌صورت صعودی یا نزولی مرتب کنید.