Sql group by having Revisited.

Today I am going to start sharing my little knowledge and learning as a new thread for all my Data Engineer, Data Architect, Data Scientist and Data Analyst friends as a bundle of Joy.

I strongly believe helping others to grow have more satisfaction than only building wealth and Money.

I have seen one common struggle in most of the Data Centric team that with advent of Genai and new Technlogies that we are struggling to get a time to keep our brain active in most basic side of programming. Here I have found 3 categories of people -

1. Who are entering Data Engineering first time
2. Who works in Data long time moved out of it and then again coming back now
3. Who never worked in Data but want to move now because there are lots of opportunities worldwide and trust me There Will be More in Coming Days!! Nobody can stop it .. Even our almighty ChatGPT .. ChatGPT will produce Millions of new Data Engineering Opportunities!! Mark my word my Friend.

Without further delay let us jump into the problem.

Today we are going to explore most common topic in sql — group by.

Please refer below my github public repo to downlaod the data,scripts and practice as per your convenient time.

sanjitkhasnobis/sql_universe: Deep Dive into World of Sql Data Engineering (github.com)

Please find below 8 problems for practice -

1. Create table software's
insert values 3 rows as ‘ChatGPT’, 2 rows as ‘Databricks’, 2 rows as ‘Airflow’
find the count of each software type.

2. find total salary, no of employees for department,
max salary, min salary, average salary
paid by each department and order by no of employees.

3. find total salary, no of employees for department,
max salary, min salary, average salary
paid by each department and for each male/female.
and order data by each department

4. find department which have number of employees
greater than 20.

5. How many people have same first name in the Company
and what is the name of those employees.

6. Find unique departments from employee's table
without using distinct keyword

7. In email field in employees table have email format in
name@domain_name.
You need write a query to find number of employees.
against specific domain name.

8. Create a Report from employees table
having maximum, minimum and average Salary
grouped by gender and region_id.

Try from your end as most of the problems are group by problems.

Now you can check your answer with my one. I hope most of them are correct.

1. create table softwares
(
sw_name varchar(200)
);

insert into softwares values
(‘ChatGpt’),
(‘ChatGpt’),
(‘ChatGpt’),
(‘Databricks’),
(‘Databricks’),
(‘Airflow’),
(‘Airflow’)

select sw_name,count(*)
from softwares
group by sw_name

2. For this problem, please make sure to create and run the table scripts mock_data.sql

select department, count(*) no_of_employees,sum(salary) sum_salary,
round(avg(salary),2) avg_salary, max(salary) max_salary, min(salary) min_salary
from employees
group by department
order by no_of_employees

3. select department,gender, count(*) no_of_employees,sum(salary) sum_salary,
round(avg(salary),2) avg_salary, max(salary) max_salary, min(salary) min_salary
from employees
group by department,gender
order by department

4. select department,count(*)
from employees
group by department
having count(*) > 20

5. select count(*),first_name
from employees
group by first_name
having count(*) > 1

6. select department
from employees
group by department

7. select substring(email,position(‘@’ in email)+1),count(*)
from employees
where email IS NOT NULL
group by substring(email,position(‘@’ in email)+1)

8. select gender,region_id,max(salary),min(salary),round(avg(salary))
from employees
group by gender,region_id
order by gender,region_id

Hope you have enjoyed the Article.

Please subscribe and like for more Article like this.

Happy Reading!! Happy Coding.