MYSQL GROUPBY WITH EXAMPLE

 create database windows_function

use windows_function

CREATING THE TABLE

create table students(

student_id int ,

student_batch varchar(40),

student_name varchar(40),

student_stream varchar(30),

students_marks int ,

student_mail_id varchar(50))

INSERTING THE VALUE IN THE TABLE

insert into students values(100 ,'electrical engineering' , 'saurabh','cs',80,'saurabh@gmail.com'),

(102 ,'electrical engineering' , 'sanket','cs',81,'sanket@gmail.com'),

(103 ,'electrical engineering' , 'shyam','cs',80,'shyam@gmail.com'),

(104 ,' electrical engineering ' , 'sanket','cs',82,'sanket@gmail.com'),

(105 ,' electrical engineering ' , 'shyam','ME',67,'shyam@gmail.com'),

(106 ,'computer science' , 'ajay','ME',45,'ajay@gmail.com'),

(106 ,'computer science' , 'ajay','ME',78,'ajay@gmail.com'),

(108 ,'computer science' , 'snehal','CI',89,'snehal@gmail.com'),

(109 ,'computer science' , 'manisha','CI',34,'manisha@gmail.com'),

(110 ,'computer science' , 'rakesh','CI',45,'rakesh@gmail.com'),

(111 ,'mechanical engineering ' , 'anuj','CI',43,'anuj@gmail.com'),

(112 ,'mechanical engineering ' , 'mohit','EE',67,'mohit@gmail.com'),

(113 ,'mechanical engineering ' , 'vivek','EE',23,'vivek@gmail.com'),

(114 ,'mechanical engineering ' , 'gaurav','EE',45,'gaurav@gmail.com'),

(115 ,'mechanical engineering ' , 'prateek','EE',89,'prateek@gmail.com'),

(116 ,'mechanical engineering ' , 'mithun','ECE',23,'mithun@gmail.com'),

(117 ,'chemical engineering' , 'chaitra','ECE',23,'chaitra@gmail.com'),

(118 ,'chemical engineering' , 'pranay','ECE',45,'pranay@gmail.com'),

(119 ,'chemical engineering' , 'sandeep','ECE',65,'sandeep@gmail.com')

select * from students

select student_batch ,sum(students_marks) from students group by student_batch

electrical engineering	241
electrical engineering	149
computer science	291
mechanical engineering	290
chemical engineering	133

select student_batch ,min(students_marks) from students group by student_batch

electrical engineering	80
electrical engineering	67
computer science	34
mechanical engineering	23
chemical engineering	23

select student_batch ,max(students_marks) from students group by student_batch

electrical engineering	81
electrical engineering	82
computer science	89
mechanical engineering	89
chemical engineering	65

select student_batch ,avg(students_marks) from students group by student_batch

electrical engineering	80.3333
electrical engineering	74.5000
computer science	58.2000
mechanical engineering	48.3333
chemical engineering	44.3333

select count(student_batch) from students

19