Grouping the Counts in SQL

hands, friendship, friends-2847508.jpg

There are a ton of different functions in SQL and some of the most common ones are COUNT() and GROUP BY. So, what exactly are they?

COUNT(): A count is an aggregate function that allows us to return a number of rows and records present within a certain chosen criteria. For example, if a table contains 20 rows of students’ names, and we use the COUNT() function, then it will count the 20 rows of students and return “20”.

GROUP BY(): A GROUP BY function groups rows that are the same in a particular column. This can also be paired with the COUNT() function. So, if we were counting distinct students’ names, and grouping them by the name column, then identical names would return more than “1”.

Here is an example using a table of students’ favorite subjects:

CREATE TABLE favorite_subjects(
student_name varchar(20),
subject varchar(20)
)
INSERT INTO favorite_subjects
VALUES
('Chris', 'Mathematics'), ('Nancy', 'English'), ('James', 'Social Studies'), ('Kimberly', 'Mathematics'), ('John', 'Science'), ('Abe', 'Social Studies')
SELECT * FROM favorite_subjects
student_name         subject
-------------------- --------------------
Chris                Mathematics
Nancy                English
James                Social Studies
Kimberly             Mathematics
John                 Science
Abe                  Social Studies

(6 rows affected)

Using the following query to count the students by their favorite subjects:

SELECT subject, COUNT(student_name) AS student_favorites
FROM favorite_subjects
GROUP BY subject
ORDER BY student_favorites DESC
subject              student_favorites
-------------------- -----------------
Mathematics          2
Social Studies       2
English              1
Science              1

(4 rows affected)

So, this query returns all the different subjects and how many students have that specific subject as their favorite.