To group queries by month name we must use datename function in sql. This function gets two parameter. The first one is interval;for day names we should write dw, for month names we shoul write m or mm. The second parameter is the date parameter. If we want just month index we can use month function too.
Lets we do some example on Library Database
Examples on Library Database
Example 1:List the number of students born each month.
|
1 2 |
Select DateName(mm,birthdate) as MName,count(*) as Count from students group by DATEName(mm,birthdate) |
Example 2:List the number of students born each month ordered by count.
|
1 2 3 |
Select DateName(mm,birthdate) as MName,count(*) as Count from students group by DATEName(mm,birthdate) order by Count |
Example 3:List the number of Mail students born each month ordered by count.
|
1 2 3 4 |
Select DateName(mm,birthdate) as MName,count(*) as Count from students where gender = 'M' group by DATEName(mm,birthdate) order by Count |
Example 4: List the Month Names and the taken book count in that month
|
1 2 |
Select DATENAME(mm,takenDate) as Months,count(*) as Count from borrows group by DATENAME(mm,takenDate) |
Example 5: List the Month Names and the taken book count in that month ordered by book count
|
1 2 3 |
Select DATENAME(mm,takenDate) as Months,count(*) as BookCount from borrows group by DATENAME(mm,takenDate) order by BookCount |
or
|
1 2 3 |
Select DATENAME(mm,takenDate) as Months,count(*) as BookCount from borrows group by DATENAME(mm,takenDate) order by 2 |
Example 6: List the Month Names of 2011 and the taken book count in that month ordered by book count
|
1 2 3 4 |
Select DATENAME(mm,takenDate) as Months,count(*) as BookCount from borrows where year(takenDate) = 2015 group by DATENAME(mm,takenDate) order by BookCount |
Example 7: List the Month Names of 2011 and the taken book count in that month ordered by book count. The Book Count must be bigger then 100
|
1 2 3 4 5 |
Select DATENAME(mm,takenDate) as Months,count(*) as BookCount from borrows where year(takenDate) = 2015 group by DATENAME(mm,takenDate) having count(*)>100 order by BookCount |
