دستور Group by در Sql

با نام و یاد خداوند. سلام خدمت کاربران گرامی
در جلسه قبل با دستور Count در sql آشنا شدیم.در این جلسه قصد داریم با دستور group by در sql آشنا شویم .

دو تا جدول به نام های Student و Course داشتیم . ابتدا با استفاده از دستور select  اطلاعات جداول خود را مشاهده می کنیم:

 

select *
from Student

جدول student

select *
from Course

جدول course

دستور group by  : با استفاده از دستور group by  می توان دسته بندی یا گروه بندی ستون ها را انجام داد .

ما در اینجا می خواهیم گروه بندی یا دسته بندی  را روی جدول Course  انجام دهیم .داخل جدول Course  4 تا ستون بنام های id ، Lesson ، Numbeer و ID_FK_Student داریم که ID_FK_Student به آی دی جدول student اشاره دارد.

نکته : گروه بندی را بر  اساس ستونی که کلید اصلی است قرار نمی دهیم  چون هیچ کدام از فیلدهای آن تکراری نیست در واقع  گروه بندی  را روی ستون هایی که دارای فیلد تکراری هستند انجام می دهیم .

با یک مثال شروع می کنیم تا با مفهوم گروه بندی آشنا شویم .

مثال :می خواهیم  مجموع ، میانگین و بزرگترین نمره دانش آموزان را بدست آوریم  ؟

ابتدا بر اساس ستون ID_FK_Student دسته بندی را انجام می دهیم .

select  ID_FK_Student
from Course
group by ID_FK_Student

دستور group by در SQL

به جدول درس ها مراجعه می کنیم :

دستور group by در SQL | جدول درس ها

مشاهده می کنید که آی دی 1030 و 1031 هر کدام 3 بار تکرار شده است ولی چون ما گروه بندی  (با استفاده از دستور group by ) انجام داده ایم دیگر فیلدهای تکراری را به ما نمایش نداده است .

ما در مثالی که زده ایم مجموع  نمرات ،میانگین و بزرگترین نمره را خواستار بودیم .گام به گام مراحل را انجام می دهیم .

 مجموع  نمرات :

select  ID_FK_Student,sum (numbeer)
from Course
group by ID_FK_Student

دستور group by در SQL | مجموع نمرات

خروجی را مشاهده کنید  دانش آموزی که کدش 1030 است مجموع نمراتش می شود 59 و دانش آموزی که کدش 1031 است مجموع نمراتش می شود 54 و مابقی  هم چون فقط یک نمره داشته اند نمره خودشان را نوشته است  .

 ولی دقت کنید به نام ستون ، نوشته است No column name   نامی برای ستون تعیین نکرده است که ما می توانیم با استفاده از as نامی را به ستون ها اضافه کنیم .

select  ID_FK_Student,sum (numbeer) as sumnumber
from Course
group by ID_FK_Student

دستور group by در SQL

خروجی را مشاهده کنید نام ستون را تغییر داده است . اگر بخواهیم که عنوان ستون را فارسی بگذاریم اجازه چنین کاری را داریم وهیچ اشکالی ندارد .

select  ID_FK_Student,sum (numbeer) as ‘جمع’
from Course
group by ID_FK_Student

 

دستور group by در SQL | استفاده از کلمه فارسی

نکته : اشکالی ندارد که در دگر نامی ، از عنوان فارسی استفاده کنیم ولی به یاد داشته باشید که داخل جدولتان اسم هیچ یک از فیلدها را فارسی انتخاب نکنید.

 میانگین نمرات :

select  ID_FK_Student,sum (numbeer) as sumnumber,
avg (numbeer) as Avgnumber
from Course
group by ID_FK_Student

دستور group by در SQL | میانگین نمرات

خروجی را مشاهده کنید برای دانش آموزانی که آی دی آن ها 1030 و 1031 بوده است و بیشتر از یک نمره داشته اند میانگین را حساب کرده است. من در اینجا یک نام برای ستون میانگین بنام  Avgnumber  انتخاب کرده ام .

 بزرگترین نمره:

select  ID_FK_Student,sum (numbeer) as sumnumber,
avg (numbeer) as Avgnumber ,max (numbeer) as  Maxnumber
from Course
group by ID_FK_Student

دستور group by در SQL | به دست آوردن بزرگترین نمره

خروجی را مشاهده کنید بزرگترین نمرات هر دانش آموز را هم نمایش داده است .

نکته : نکته ای  که درباره ی دستور group by در sql وجود دارد  این است که ، وقتی از دستور group by در sql استفاده می کنید و خواستید روی دستوری شرط بگذارید که از توابع تجمعی در آن استفاده کنید  باید  به جای استفاده از دستور where از دستور having استفاده کنید . ولی در بقیه موارد می توان از دستور where استفاده کرد .

توابع تجمعی شامل avg , sum , count , min , max  و … می باشند که در اصطلاح به توابع تجمعی یا aggregate functions  معروف هستند.

مثال : دستوری بنویسید که مجموع ، میانگین و بزرگترین نمره دانش آموزانی را نمایش دهد که میانگین نمره ی آن دانش آموز بزرگتر از 18 باشد ؟

select  ID_FK_Student,sum (numbeer) as sumnumber,
avg (numbeer) as Avgnumber ,max (numbeer) as  Maxnumber
from Course
group by ID_FK_Student
having avg (numbeer) >18

دستور group by در SQL

 

خروجی را مشاهده می کنیم فقط انش آموزانی با آ دی 1030 و 2031 را نمایش می دهد چون میانگین نمرات آن ها بیشتر از 18 است .

مثال : دستوری بنویسیم که مجموع ، میانگین و بزرگترین نمره دانش آموزانی با کد 1030 را نمایش دهد به شرطی که میانگین نمره آن دانش آموز بزرگتر از 18 باشد ؟

select  ID_FK_Student,sum (numbeer) as sumnumber,
avg (numbeer) as Avgnumber ,max (numbeer) as  Maxnumber
from Course
where ID_FK_Student=1030
group by ID_FK_Student
having avg (numbeer) >18

 

دستور group by در SQL | مثال

مشاهده می کنید که در موردی  که از  تابع avg استفاده  کرده ام ، دستور شرطی having را به کار برده ام و در موردی که شرط فقط روی ستون خاصی بوده ایست ، دستور where به کار برده ام .

نکته ی دیگری  نیز درباره ی دستور group by در sql وجود دارد  که با یک مثال آن را بررسی می کنیم .

مثال: می خواهیم در جدول Course  دسته بندی را بر اساس ستون id انجام دهیم:

select id
from Course
group by id

 

دستور group by در SQL | دسته بندی براساس id

خروجی را مشاهده می کنید کاملا صحیح است .

ولی اگر من خواسته باشم بر اساس ستون ID_FK_Student  دسته بندی را انجام دهد ببینیم چه اتفاقی می افتد .

select id
from Course
group by ID_FK_Student

دستور group by در SQL

مشاهده می کنید که پیغام خطا می دهد . چرا این اجازه را نمی دهد ؟

نکته : هرفیلدی را جلوی دستور select قرار دهید  باید آن فیلد را نیز جلوی دستور group by هم قرار بدهیم  . به جز فیلدهایی که داخل یکی از توابع تجمعی قرار دارند .

select id
from Course
group by ID_FK_Student,id

دستور group by در SQL

مشاهده می کنید که فقط با گذاشتن فیلد جلوی دستور  select  ، جلوی دستور group by   دستور به درستی اجرا می شود .

امیدواریم با دستور group by در SQL و نحوه استفاده  آن به خوبی آشنا شده باشید .

 

با  آموزش مبانی SQL Server در خدمت شما هستیم. لطفا سوالات ، نظرات و پیشنهادات خود را در قسمت دیدگاه ها با ما در میان بگذارید.

به این مقاله چند تا ستاره میدی؟
[تعداد رای: 34 امتیاز: 4.4]