Forem: lateef sulaiman

Mastering SQL: Understanding Group By and Order By

lateef sulaiman — Sat, 27 Jul 2024 08:38:51 +0000

Introduction
In this article, I'll walk you through two of the most important concepts in SQL: GROUP BY and ORDER BY. Mastering these concepts will make query writing much easier and more efficient for you. We will explore how to use them, when to use them, and where they can be applied effectively. By the end of this article, you'll have a clear understanding of these concepts and be able to use them proficiently in your SQL queries.

GROUP BY is an essential tool for Data Analysis. GROUP BY groups rows with the same values in specified columns into summary rows. It is mostly used with ‘Aggregate Functions’ like ‘COUNT’, ‘SUM’, ‘AVG’, ‘MAX’, and ‘MIN’.
Think of GROUP BY as sorting things into different categories. Imagine you have a big box filled with balls of different colors and sizes. To organize them, you decide to separate them into smaller boxes based on their color. You would end up with a box of blue balls, a box of red balls, and so on. In this scenario, you are grouping the balls by their color. Similarly, you could also organize the balls by their size, placing all the small balls in one box, the medium balls in another, and the large balls in yet another. This is exactly what GROUP BY does in SQL: it organizes your data into categories based on one or more columns.
Example
Suppose I have a table named 'Student_Data' with the following data:

Name	Gender	Birth_Month	Age
Mike	Male	January	12
Alex	Male	January	14
Anita	Female	April	12
Gbenga	Male	February	15
Amanda	Female	June	14
Folarin	Male	February	13
Lateef	Male	September	10
Oyiza	Female	September	10
Mark	Male	May	11
Valerie	Female	June	13
Chioma	Female	April	15

I can group these data by Gender, Birth_Month, Age and even Name.

Example Queries and Results:

Group by Gender ```

SELECT Gender
FROM Student_Data
GROUP BY Gender;

Result: 

Gender
------
|Male   |
|Female |

2.

SELECT Birth_Month
FROM Student_Data
GROUP BY Birth_Month;

Result:

Birth_Month
-----------
|January|
|April|
|February|
|June|
|September|
|May|

3.

SELECT name
FROM Student_Data
GROUP BY name;

Result:

Name
--------
|Mike|
|Alex|
|Anita|
|Gbenga|
|Amanda|
|Folarin|
|Lateef|
|Oyiza|
|Mark|
|Valerie|
|Chioma|

**Group By and Aggregate Functions**
Now, let's talk about GROUP BY and Aggregate Functions. These are used to aggregate data, providing summarized information from your dataset.

Still going by the above scenario with the colored balls, if we are interested in knowing the details of each box, we can use what we call Aggregate Functions. How does the Aggregate Function work? With Aggregate Functions, we can determine the number of balls in each box, the biggest ball in each box, the smallest ball in each box, the average size of the balls in each box, and the total size of the balls in each box.

In SQL, Aggregate Functions like COUNT, MAX, MIN, AVG, and SUM are used with the GROUP BY clause to perform these calculations.

From the Student_Data above, we can use GROUP BY and Aggregate Functions to calculate the number of students by gender, the average age of students, the age of the oldest student, the age of the youngest student, and the sum of the students' ages by gender.

Example Queries and Results:

- Number of students by gender: We can use the COUNT function to know the number of students by gender.

SELECT Gender, COUNT(*) AS Number_of_Students
FROM Student_Data
GROUP BY Gender;

Result:

| Gender | Number_of_Students 
|------- | ------  
| Male   |  6   
| Female |  5   
|    |          



- Average Age of Students by Gender: We can use the 'AVG' function to calculate the average age of students by gender.

SELECT Gender, AVG(Age) AS Average_Age
FROM Student_Data
GROUP BY Gender;

Result: 

| Gender | Average_Age
| ------ | -------
| Male   | 12.5
| Female | 12.8
|        | 


- Oldest Student by Gender: We can use the 'MAX' function to find the age and name of the Oldest Student.

SELECT Gender, MAX(Age) AS Oldest_Student
FROM Student_Data
GROUP BY Gender;

Result: 

| Gender | Oldest_Student
| ------ | ----------
| Male   | 15
| Female | 15
|        | 


- Sum of Students' ages by gender: We can use the 'SUM' function to calculate the total ages of Students by Gender.

SELECT Gender, SUM(Age) AS Total_Ages
FROM Student_Data
GROUP BY Gender;


Result:


| Gender | Total_Ages
| ------ | -------
| Male   | 75
| Female | 64

By using 'GROUP BY' with AGGREGATE FUNCTIONs we can easily get summarized insights, helping us to better analyze and understand our data. 

**ORDER BY**
Now that we understand how to use 'GROUP BY' to organize data into categories, let's talk about 'ORDER BY'.'ORDER BY' in SQL is used to arrange data in ascending or descending order based on the values specified. 

Think of 'ORDER BY' as arranging a list of names alphabetically or arranging a group of students by their height.

Example: Considering our 'Student_Data' table. We can decide to list the students by their ages in ascending order.

SELECT Name,Age
FROM Student_Data
ORDER BY Age;

Result:

| Name    | Age
|------   | -----
| Lateef  | 10
| Oyiza   | 10
| Mark    | 11
| Mike    | 12
| Anita   | 12
| Folarin | 13
| Valerie | 13
| Alex    | 14
| Amanda  | 14
| Gbenga  | 15
| Chioma  | 15 

**Combining GROUP BY and ORDER BY**
We can combine 'GROUP BY' and 'ORDER BY' to get more insight from our 'Student_Data' Table.

We can find the average age of students for each gender and then list these gender groups in descending order of their average age.

Example Query:

SELECT Gender, AVG(Age) AS Average_Age
FROM Student_Data
GROUP BY Gender
ORDER BY Average_Age DESC;

Result:

| Gender | Average_Age
| ------ | ---------
| Female | 12.8
| Male   | 12.5

In the query above:

- The 'GROUP BY' clause groups the student by their Gender 

- The 'AVG' function calculates the average age for each gender group.

- The 'ORDER BY' clause sorts the result by the average age in descending order. 

By combining 'GROUP BY' and 'ORDER BY' we can sort our grouped data, making it easier to analyze and understand the results. 

**CONCLUSION**
Understanding how to use 'GROUP BY' and 'ORDER BY' in SQL is crucial for effective Data Analysis. These powerful tools allow us to organize, summarize and sort data in meaningful ways. 'GROUP BY' can help us  categorize our data into groups, Aggregate Functions can help us extract valuable insights from these groupings. The ORDER BY clause then helps us to sort and present our results in an ordered fashion, making our data easier to interpret and analyze.

Solving the SQL Murder Mystery: A Step-by-Step Guide

lateef sulaiman — Tue, 16 Jul 2024 12:30:03 +0000

Maybe I should consider a career as a detective! In this article, I am going to walk you through how I solved a mystery murder case from SQL Murder Mystery. Let us dive into the crime scene and uncover the truth step by step.

The Mystery
A crime has taken place and the detective needs your help. The detective gave you the crime scene report but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan. 15, 2018, and that it took place in SQL City. Let's start by retrieving the corresponding crime scene report from the police department’s database. A schema was provided;

The Initial Clues
I only remembered the type of crime, the city, and the date. So, I started with this query:



SELECT * FROM crime_scene_report 
WHERE type = 'murder' 
AND city = 'SQL City' 
AND date = '20180115';

Here’s what I found:
"Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness named Annabel lives somewhere on "Franklin Ave""

Great! So We have two witnesses. The first one lives at the last house on Northwestern Dr, and the second one, Annabel, lives on Franklin Ave.

Finding the First Witness
To identify the first witness, I run this query:



SELECT * FROM person 
WHERE address_street_name = 'Northwestern Dr' 
ORDER BY address_number DESC 
LIMIT 1;

From this query, I was able to identify Morty Schapiro with person_id 14887 and license_id 118009 as my first witness.

Finding the Second Witness
For the second witness, Annabel, I ran:



SELECT * FROM person 
WHERE name LIKE 'Annabel%' 
AND address_street_name = 'Franklin Ave';

From this query, I was able to get my second witness Annabel Miller with person_id 16371 and license_id 490173.

Gathering Witness Statements
Next, I needed to find out what each witness saw. I run a query to know what Morty Schapiro witnessed:



SELECT * FROM interview 
WHERE person_id = '14887';

Morty's statement: I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z". Only gold members have those bags. The man got into a car with a plate that included "H42W".

Now, let’s run a query to get Annabel Miller's statement:



SELECT * FROM interview 
WHERE person_id = '16371';

Annabel's statement: I saw the murder happen and I recognized the killer from my gym when I was working out last week on January the 9th.

Identifying the Killer
From the statements above, I know the following:

The murderer is a "Get Fit Now Gym" gold member.
The membership number starts with "48Z".
The car plate includes "H42W".
The murderer was at the gym on January 9th.

First, I checked for gym check-ins on January 9th with this query:



SELECT * FROM get_fit_now_check_in 
WHERE check_in_date = '20180109' 
AND membership_id LIKE '48Z%';

I got two results with membership_id 48Z7A and 48Z55. Both are gold members, so I need to narrow it down using the car plate information.

Narrowing Down the Suspects
First, I retrieved the license details for both suspects:



SELECT * FROM person 
WHERE id IN (28819, 67318);

I got two result from running the query above: Jeremy Bowers with license_id 423327 and Joe Germuska with license_id 173289.

Next, I checked their car plates:



SELECT * FROM drivers_license 
WHERE id IN (423327, 173289);

From my result, I was able to identify Jeremy Bowers as the Murderer.

The Real Mastermind
Upon further investigation, I found that Jeremy Bowers was hired by a woman with distinct characteristics. Let’s run a query to see his statement:



SELECT * FROM interview 
WHERE person_id = '67318';

Jeremy's statement: I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.

Using this information, I searched for the woman:



SELECT * FROM drivers_license 
WHERE hair_color = 'red' 
AND gender = 'female' 
AND car_make = 'Tesla' 
AND car_model = 'Model S';

I got 3 results from this, all with the same height as described by Jeremy. From Jeremy's statement the mastermind attended SQL Symphony Concert thrice.
I run a query to check for the person_id on the person table.



SELECT * FROM person
WHERE license_id in (202298,291182,918773);

I got the license_id and name of the 3 possible mastermind from the query above; Red korb with id 78881, Regina George with id 90700 and Miranda Priestly with id 99716.

Finally, I run a query to check their attendance at the SQL Symphony Concert, to see who attended thrice;



SELECT * FROM facebook_event_checkin 
WHERE person_id IN (78881, 90700, 99716);

From my query, I was able to get that The mastermind is Miranda Priestly with person_id 99716, who attended the concerts three times.

Conclusion
The murderer is Jeremy Bowers, but the mastermind behind the crime is Miranda Priestly. This was a thrilling case to solve, combining SQL queries with logical deduction. It was an exciting journey to catch the real villain behind the murder.

Would you like to try solving it yourself? Head over to SQL Murder Mystery and put your detective skills to the test!