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.
