MySQL GROUP BY

How to use MySQL GROUP BY?

As name suggested MySQL GROUP BY is used to group the rows with the same values of defined columns added in GROUP BY clause.

It is often used when we are using the mysql aggregate functions such as COUNT(), SUM(), MIN(), MAX(), AVG(). We will see how we can use GROUP BY on this functions one by one.

Syntax for group by is as follows:

SELECT
  column_name1, column_name2..,
FROM
  schema.table_name
WHERE condition(s)
GROUP BY column_name1, column_name2;

We will use same student_report table created in MySQL joins to understand the group by clause.

1. COUNT():

We will count total number of rows present for each subject in the table using count() method grouping by subject.

SELECT
  subject, COUNT(subject) AS subject_rows
FROM
  student_report
GROUP BY subject;

• Note that whatever columns we are selecting except the one on which we are using any aggregate function needs to be added in the GROUP BY clause. Else we will get the error as follows.
• Error Code: 1140. In aggregated query without GROUP BY 
• Also we can use any column other that subject in the ‘count(subject)’ function like count(marks) or count(1). This will give the same result as above.
• Let’s see if we are not selecting any column beside aggregate function column, this will return count of total rows present in the ‘student_report’ table.

2. SUM():

we will write the query to get the sum or marks of each subject using sum function and group by clause

SELECT
  subject, SUM(marks)
FROM
  test_schema1.student_report
GROUP BY subject;

3. MIN():

We will get the minimum marks for each subject using min function and group by clause

SELECT
  subject, min(marks) AS subject_rows
FROM
  student_report
GROUP BY subject;

4. MAX():

We will get the maximum marks for each subject using max function and group by clause

SELECT
  subject, max(marks) AS subject_rows
FROM
  student_report
GROUP BY subject;

5. AVG():

To get the average marks for each subject we can use avg function with group by clause.

SELECT
  subject, avg(marks) AS subject_rows
FROM
  student_report
GROUP BY subject;

-A blog by Shwetali Khambe