Tutorials

SQL GROUP BY


SQL GROUP BY


In Sql, GROUP BY statement is mostly used along with the aggregate functions like COUNT, MAX, MIN, etc to group the result based on one or multiple columns.

Syntax of GROUP BY

SELECT aggregateFunction(column_name)
FROM table_name
GROUP BY column_name;

aggregateFunction can be COUNT, MAX, MIN, SUM, AVG, etc

We will learn more in examples given below.

Important points

  • We can specify multiple columns in GROUP BY.
  • We can use more than one aggregate function for grouping the result-set.
  • If ORDER BY is used then it should only be used after GROUP BY.

 

Example on GROUP BY

We will use 'tih_Courses' table for demonstration purpose

tih_Courses
Course_ID Course_Name Professor Mode_of_Training Course_Fee
001 .Net Mukesh Tiwari Online and Offline 550
002 Java Rajneesh Shukla Online and Offline 550
003 ANSI SQL Rajneesh Shukla Online 500
004 Basics of Computer Shweta  Online 350
005 Blogging Tutorial Puja  Online 250
006 JSP Alok Pandey Online and Offline 350
007 Servlet Mukesh Tiwari Online and Offline 350
008 Spring Anup Shukla Online 650

Query-1: Write a sql query to fetch the total courses offered by each Professor from tih_Courses table

SELECT COUNT(Course_Name)AS Courses, Professor
FROM tih_Courses
GROUP BY Professor;

After above sql query gets executed, we will get following result:

Courses Professor
2 Mukesh Tiwari
2 Rajneesh Shukla
1 Shweta 
1 Puja 
1 Alok Pandey
1 Anup Shukla
Since COUNT(Course_Name) is queried as ALIAS (or AS) so in output is returned as Courses.
Above result shows data as total count of courses offered by each professor at tutorialsinhand.com
 
Query-2: Write a sql query to fetch the total courses offered by each Professor in sorted manner from tih_Courses table.
SELECT COUNT(Course_Name)AS Courses, Professor 
FROM tih_Courses 
GROUP BY Professor
ORDER BY Professor;

Now the above result obtained in query one will be returned as sorted by Professor name. Default sorting is ascending order.



Please Share this page
Views : 102
Like every other website we use cookies. By using our site you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service. Learn more Got it!