دستور Group by در Sql
با نام و یاد خداوند. سلام خدمت کاربران گرامی
در جلسه قبل با دستور Count در sql آشنا شدیم.در این جلسه قصد داریم با دستور group by در sql آشنا شویم .
دو تا جدول به نام های Student و Course داشتیم . ابتدا با استفاده از دستور select اطلاعات جداول خود را مشاهده می کنیم:
select *
from Student
select *
from 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
به جدول درس ها مراجعه می کنیم :
مشاهده می کنید که آی دی 1030 و 1031 هر کدام 3 بار تکرار شده است ولی چون ما گروه بندی (با استفاده از دستور group by ) انجام داده ایم دیگر فیلدهای تکراری را به ما نمایش نداده است .
ما در مثالی که زده ایم مجموع نمرات ،میانگین و بزرگترین نمره را خواستار بودیم .گام به گام مراحل را انجام می دهیم .
مجموع نمرات :
select ID_FK_Student,sum (numbeer)
from Course
group by ID_FK_Student
خروجی را مشاهده کنید دانش آموزی که کدش 1030 است مجموع نمراتش می شود 59 و دانش آموزی که کدش 1031 است مجموع نمراتش می شود 54 و مابقی هم چون فقط یک نمره داشته اند نمره خودشان را نوشته است .
ولی دقت کنید به نام ستون ، نوشته است No column name نامی برای ستون تعیین نکرده است که ما می توانیم با استفاده از as نامی را به ستون ها اضافه کنیم .
select ID_FK_Student,sum (numbeer) as sumnumber
from Course
group by ID_FK_Student
خروجی را مشاهده کنید نام ستون را تغییر داده است . اگر بخواهیم که عنوان ستون را فارسی بگذاریم اجازه چنین کاری را داریم وهیچ اشکالی ندارد .
select ID_FK_Student,sum (numbeer) as ‘جمع’
from Course
group by ID_FK_Student
نکته : اشکالی ندارد که در دگر نامی ، از عنوان فارسی استفاده کنیم ولی به یاد داشته باشید که داخل جدولتان اسم هیچ یک از فیلدها را فارسی انتخاب نکنید.
میانگین نمرات :
select ID_FK_Student,sum (numbeer) as sumnumber,
avg (numbeer) as Avgnumber
from Course
group by ID_FK_Student
خروجی را مشاهده کنید برای دانش آموزانی که آی دی آن ها 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 استفاده می کنید و خواستید روی دستوری شرط بگذارید که از توابع تجمعی در آن استفاده کنید باید به جای استفاده از دستور 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
خروجی را مشاهده می کنیم فقط انش آموزانی با آ دی 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
مشاهده می کنید که در موردی که از تابع avg استفاده کرده ام ، دستور شرطی having را به کار برده ام و در موردی که شرط فقط روی ستون خاصی بوده ایست ، دستور where به کار برده ام .
نکته ی دیگری نیز درباره ی دستور group by در sql وجود دارد که با یک مثال آن را بررسی می کنیم .
مثال: می خواهیم در جدول Course دسته بندی را بر اساس ستون id انجام دهیم:
select id
from Course
group by id
خروجی را مشاهده می کنید کاملا صحیح است .
ولی اگر من خواسته باشم بر اساس ستون ID_FK_Student دسته بندی را انجام دهد ببینیم چه اتفاقی می افتد .
select id
from Course
group by ID_FK_Student
مشاهده می کنید که پیغام خطا می دهد . چرا این اجازه را نمی دهد ؟
نکته : هرفیلدی را جلوی دستور select قرار دهید باید آن فیلد را نیز جلوی دستور group by هم قرار بدهیم . به جز فیلدهایی که داخل یکی از توابع تجمعی قرار دارند .
select id
from Course
group by ID_FK_Student,id
مشاهده می کنید که فقط با گذاشتن فیلد جلوی دستور select ، جلوی دستور group by دستور به درستی اجرا می شود .
امیدواریم با دستور group by در SQL و نحوه استفاده آن به خوبی آشنا شده باشید .
با آموزش مبانی SQL Server در خدمت شما هستیم. لطفا سوالات ، نظرات و پیشنهادات خود را در قسمت دیدگاه ها با ما در میان بگذارید.
به این مقاله چند تا ستاره میدی؟[تعداد رای: 34 امتیاز: 4.4]
سلام و خسته نباشین
لطفا در لابلای جلسات آموزشی خوبتون، بعضی از تریک های کاربردی رو هم آموزش بدین.
در ضمن منتظر جلسات بعدی و خصوصا مبحث اسفاده از تاریخ شمسی و میلادی و توابع مربوطه هستیم.
با تشکر
مرسی
سلام
من یه جدول دانش آموز دارم با فیلدهای عادی یه جدول هم برای کلاس دارم. دانش آموز با کلاس رابطه چند به چند داره. واسه همین یه جدول میانی تعریف کردم که بتونم حضور دانش آموزها در کلاسها رو در هر تاریخ،حضور و غیاب کنم. واسه همین یه جدولی به نام hozur ایجاد کردم با فیلدهای (تاریخ، آیدی دانش آموز و آیدی کلاس).
حالا می خوام یه گزارش چاپ کنم که بره از دیتابیس ببینه تو هر روز کدام دانش آموز ها تو کلاس شماره ۱ ثبت نام کردند. تو روز دیگه و تو روز دیگه کدام ها؟
لازم به ذکره من نمی دونم چه روزهایی کلاس برگزار شده و چه روزهایی نشده؟ می خوام کوئری خودش روزها رو پیدا کنه و بگه که تو هر روز کدام دانش آموزان تو کلاس شرکت کردند.
یه توضیح هم اینکه اگر می خواستم بدونم هر روز، چند دانش آموز تو کلاس شماره n شرکت کردند کوئری زیر رو می زدم.
select tarikh,count(student_id) from class where class_id=n group by tarikh
اما من تعداد نمی خوام. آیدی دانش آموزانی که تو هر کدوم از روزها شرکت کردند رو می خوام.
باید چطوری کوئری بزنم؟ اصلا می شه با group by همچین کوئری ای رو زد؟
سلام و عرض ادب. بهترین روش برای انجام این کار نوشتن دو تا کوئری هست که با تاریخ با هم در ارتباط هستند و با استفاده از Sub کوئری می توانید این دو کوئری را در یک فرم به نمایش در بیاورید. البته هنوز آموزش های مربوط به این موضوعات بر روی سایت قرار نگرفته است.
با تشکر از همراهی شما
امکانش هست کوئری رو برام بنویسید؟
چون من در مورد ساب کوئری مطالعه کردم. نتونستم ازش استفاده کنم
دمتون گرم اقا
سلام خسته نباشید
من یک کوئری میخوام بنویسم بدون استفاده از group by تعداد سفارشات ثبت شده در هر یک از روزهای هفته رو نمایش بده،ممنون میشم کمک کنید
سلام و عرض ادب. اول که شما باید یه اطلاعاتی راجع به کلیات و اینکه چه جدولی و چه فیلدهایی در جدول شما ذخیره می شود به ما بدهید تا بتوانیم شما را راهنمایی کنیم. دوم اینکه شما می توانید با گذاشتن شرط Where سطرهایی را که نیاز دارید فیلتر کنید.
با تشکر از همراهی شما
سلام. نظر شخصی که لینک سایت شما رو یه جای دیگه بارگذاری کرد.
لطفا از slug انگلیسی به جای فارسی استفاده بکنید تا کپی کردن لینک سایتتون برام مشکل ساز نشه. یا کوتاه کننده لینک درست کنید
سلام و عرض ادب
به این موضوع رسیدگی می شود.
با تشکر از همراهی شما
salam kheli khoob va mofid bood
vghan khaste nabashid
سلام وقت بخیر ببخشید بنده یکسری اطلاعات دارم که اطلاعات بر اساس parent group به دو دسته تقسیم بندی شده حال میخواهم میانگین گروه اول رو از میانگین گروه ۲ کم کنم فانکشن این دستور رو لطف میکنید بگید
سلام خیلی ممنونم از این آموزش خوبتون. خیلی کاربردی بود برای من
نحوه آموزش واقعا عالیه
: ( چقدر تو در تو و پیچیده شد دستورات
سلام
من کوئری میخوام بنویسم که اسامی دانش آموزان یک درس خاص رو به همراه نظراتشون نمایش بده
سلام
من کوئری میخوام بنویسم که اسامی دانش آموزان یک درس خاص رو به همراه نظراتشون نمایش بده
لطف می کنید راهنمایی کنید
سلام خیلی عالی توضیح دادید
ممنونم
لذت بردم
هیچ ابهامی نداره
سلام و عرض ادب
از انرژی مثبت شما سپاسگزاریم
سلام وقت بخیر.من دانشجوی یکی از دانشگا های لندنم و یکی از درسامون sql هستش.هر سابجکتی که استادمون درس میده رو میام از پیج شما یاد میگیرم یعنی واقعا ازتون ممنونم این استاد ما من نمیدونم چجوری استاد شده اصصلا درس دادن بلد نیست.حیفه شهریه ایی که دادم.واقعا کارتون درسته یک دنیا ازتون ممنونم.اگه بدونید چقدر به من کمک کردید و چه چیزای مفیدی از طریق پیجتون به من یاد دادید! بوس به کل تیمتون.
سلام و عرض ادب
از انرژی مثبت شما سپاسگزاریم و به امید روزی که تمام عزیزان ایرانی بتوانند در همه جا بدرخشند و به بشریت خدمت کنند! بوس به شما و استادتون
سلام با تشکر فراوان از زحمات شما
سلام باتشکر از آموزش خوبتون
من میخواستم یه دستور بنویسم که مانده حساب از دو فیلد بدهکار و بستانکار را نشون بده . میشه لطفا راهنمایی کنید
خیلی ممنون از توضیحات شما
سلام و عرض ادب
رضایت شما موجب خرسندی ماست و حمایت شما برای ما بسیار ارزشمند است.
با تشکر از همراهی شما
فوق العاده مفید و آموزنده بود ممنون.
سلام و عرض ادب
رضایت شما موجب خرسندی ماست و حمایت شما برای ما بسیار ارزشمند است.
با تشکر از همراهی شما
سلام
من میخواستم با اکسس یه کویری بسازم که یکی از ستونهاش مانده حساب را به من نشان بده لطفا راهنمایی بفرمایید
باتشکر