In this post we’ll write many example about group by clause on library database. Some of this examples will be easy, and the others ones will be expert. With this examples you can learn how the group by clause used.
TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK
Sql Group By Clause Examples on Library Database
Example 1: List the class names and student count of each class.
|
1 2 |
Select class,count(*) as StudentCount from students group by class |
Result:
20 rows listed.
Example 2: List the genders and student count of each gender
|
1 2 |
Select gender,count(*) as StudentCount from students group by gender |
Result:
2 rows listed.
Example 3: List the genders and student count of each gender
|
1 2 |
Select gender,count(*) as StudentCount from students group by gender |
Result:
2 rows listed.
Example 4: List the numbers of boys and girls in each class.
|
1 2 |
Select class,gender,count(*) as StudentCount from students group by gender,class |
Result:
40 rows listed. There are 16 girls and 7 boys in 10A class.
Example 5: List only the number of female students in each class.
|
1 2 3 4 |
Select class,gender,count(*) as StudentCount from students where gender = 'F' group by gender,class |
Result:
20 rows listed.
Example 6: List the class names and number of students which the number of students more than 30.
|
1 2 3 4 |
Select class,count(*) as StudentCount from students group by class having count(*) >= 30 |
Result:
2 rows listed.
Example 7: List the name and surname of the students and the number of books they read.
|
1 2 3 4 |
Select name,surname,count(*) BookCount from students,borrows where students.studentId = borrows.studentId group by students.studentId,name,surname |
Not: We must add the studentId to group by clause because there may be the students whoes has same name and surname.
Solution 2 with join:
|
1 2 3 4 |
Select name,surname,count(*) BookCount from students join borrows on students.studentId = borrows.studentId group by students.studentId,name,surname |
Result:
504 rows listed.
Example 8: List the name and surname of the students and the number of books they read sorted by BookCount.
|
1 2 3 4 5 |
Select name,surname,count(*) BookCount from students,borrows where students.studentId = borrows.studentId group by students.studentId,name,surname order by BookCount |
Solution 2 with join:
|
1 2 3 4 5 |
Select name,surname,count(*) BookCount from students join borrows on students.studentId = borrows.studentId group by students.studentId,name,surname order by BookCount |
Result:
504 rows listed. Have you noticed. Students who have never read a book have not been listed. Let’s list them in the next example.
Example 9: List the name and surname of the students and the number of books they read sorted by BookCount. Also list the students who have never read a book.
|
1 2 3 4 5 |
Select name,surname,count(borrowId) BookCount from students left join borrows on students.studentId = borrows.studentId group by students.studentId,name,surname order by BookCount |
Not: We changed count(*) to count(BorrowId). If we don’t change, BookCount Column’s value in the first row will have been 1. It is an important difference.
Result:
505 rows listed. There is one student who have never read a book.
Example 10:
List the number of books read grouped by date quarter
|
1 2 3 |
Select Year(takendate) as Year, datepart(qq,takendate) as Quarter,count(*) as Count from borrows group by Year(takendate),datepart(qq,takendate) |
Result Of Query
Example 11:
List the student count gruped by date quarter
|
1 2 3 |
Select Year(birthdate) as Year, datepart(qq,birthdate) as Quarter,count(*) as Count from students group by Year(birthdate),datepart(qq,birthdate) |
Result Of Query
