Group by query in MSSQL Table |
In our student database we have seen how count query works. Now we will discuss how to display total number of records based on GROUP By SQL command. You can read more on GROUP BY command here.
Let us try to display total number of students in each class. Here we will display class name and number of students in the class.
You can read the basic SELECT QUERY here , we will only change the sql part here.
rs1.open " select COUNT(class) as no,class from student group by class " , conn
Within the Do While loop we will keep this code to display the data.
Response.Write rs1("class") & rs1("no") & "<br>"
GROUP BY command with SUM
We can find out total mark of students in each class by using SUM and GROUP BY sql commands. Here is the query and display part
rs1.open " select SUM(mark) as no,class FROM student GROUP BY class " , conn
GROUP BY command with AVG
Same way we can get teh average mark in each class by using AVG command with GROUP BY
rs1.open " select AVG(mark) as no,class FROM student GROUP BY class " , conn
GROUP BY command with MAX and MIN
Same way we can find out the highest mark in each class by using MAX command and the minimun mark by using MIN query
rs1.open " select MAX(mark) as no,class FROM student GROUP BY class " , conn
rs1.open " select MIN(mark) as no,class FROM student GROUP BY class " , conn
|
