🎓 𝐀𝐜𝐜𝐞𝐧𝐭𝐮𝐫𝐞 𝐅𝐑𝐄𝐄 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 😍

Boost your skills with 100% FREE certification courses from Accenture!

📚 FREE Courses Offered:
1️⃣ Data Processing and Visualization
2️⃣ Exploratory Data Analysis
3️⃣ SQL Fundamentals
4️⃣ Python Basics
5️⃣ Acquiring Data

𝐋𝐢𝐧𝐤 👇:- 

https://pdlink.in/4yJKnBy

✅ Learn Online | 📜 Get Certified

🚀 Data Analyst Roadmap — Part 9

📊 Excel — Level 8: PivotTables, PivotCharts & Interactive Analysis

Now that you understand Excel formulas and dynamic functions, it's time to learn one of the most important Excel features for Data Analysts: PivotTables.

A PivotTable allows you to take a large dataset and quickly summarize it without writing complicated formulas.

For example, imagine you have 50,000 sales transactions. Your manager asks: "Show me total sales by region, product category, and month." Doing this manually would take a lot of time. With a PivotTable, you can summarize the data in seconds.

1️⃣ What Is a PivotTable?

A PivotTable is an Excel tool that lets you summarize, group, compare and analyze large datasets.

Raw data example:

Order ID | Date | Region | Product | Sales | Profit

1001 | Jan | North | Laptop | 80,000 | 12,000

1002 | Jan | South | Mouse | 2,000 | 500

Instead of manually calculating totals, create a PivotTable.

2️⃣ Creating a PivotTable

First select your dataset.

Then: Insert → PivotTable → Usually select New Worksheet → OK.

You'll see four main areas: Rows, Columns, Values, Filters. These four areas are the foundation.

3️⃣ Understand the Rows Area

Rows determines what you want to group by.

Drag Region → Rows → You get North, South, West grouped.

4️⃣ Understand the Values Area

Values contains the calculation. Drag Sales → Values → Sum of Sales.

Region | Total Sales → North 155,000, South 92,000, West 5,000.

Now you've answered: "How much did each region sell?"

5️⃣ Understand the Columns Area

Allows you to compare categories horizontally. Region → Rows, Product → Columns, Sales → Values → You get Region x Product matrix.

6️⃣ Understand the Filters Area

Lets you filter entire PivotTable.

Region → Rows, Sales → Values, Year → Filters → Select 2026 to see only 2026 results.

7️⃣ The Four PivotTable Areas

Rows → What do I want to group by?

Columns → What do I want to compare across?

Values → What calculation do I want?

Filters → What do I want to filter?

8️⃣ Change the Calculation

Right-click value → Value Field Settings → Choose Sum, Count, Average, Max, Min, etc. e.g., "What is average sales per order?" → Change to Average.

9️⃣ Sum vs Count in PivotTables

Sum of Sales = 100,000, Count = 3, Average = 33,333.33.

Always make sure aggregation matches business question.

🔟 Show Values as % of Total

Right-click Sales values → Show Values As → % of Grand Total → North 50%, South 30%, West 20%.

Useful for contribution analysis.

1️⃣1️⃣ Group Dates in PivotTables

Right-click a date → Group → Years, Quarters, Months, Days.

Makes time-based analysis easier.

1️⃣2️⃣ Analyze Monthly Sales

Order Date → Rows, Sales → Values, Group by Months → Jan 120K, Feb 145K, Mar 170K etc.

1️⃣3️⃣ Analyze Sales by Region and Month

Rows → Region, Columns → Month, Values → Sales → Matrix to identify best/worst region and trends.

1️⃣4️⃣ Sorting PivotTable Results

Sort Largest → Smallest to make best performers stand out.

1️⃣5️⃣ Top 10 Analysis

Use Value Filters → Top 10 to show top 10 customers/products/regions.

1️⃣6️⃣ Slicers

Slicers make PivotTables interactive.

Add slicer for Region → Clickable North/South/East/West → PivotTable updates. Easier for non-technical users.

1️⃣7️⃣ Multiple Slicers

Add Region, Category, Year slicers → User selects Region: North, Category: Electronics, Year: 2026 → Shows only relevant info. Foundation of interactive dashboard.

1️⃣8️⃣ PivotCharts

A chart connected to a PivotTable.

📈 Line Chart for sales by month,

📊 Column Chart for sales by region.

Automatically responds to filters and slicers.

1️⃣9️⃣ Choosing the Right Chart

Compare categories → Bar/Column Chart

Show trends over time → Line Chart

Show contribution → Bar or Pie/Donut for small categories

Analyze relationships → Scatter Plot

2️⃣0️⃣ Drill Down

Year → Quarter → Month → Day. Move from high-level view to detailed view.

2️⃣1️⃣ Drill Through to Source Data

Double-click a value to see underlying records contributing to that value. Useful for investigating unexpected numbers.

2️⃣2️⃣ Refreshing PivotTables

PivotTables don't auto-update. Right-click → Refresh or Data → Refresh All. Using Excel Table as source makes refresh easier.

2️⃣3️⃣ PivotTable Best Practice

Source data should have:

✅ Headers

✅ No blank rows

✅ Consistent data types

✅ One record per row

✅ One field per column

✅ No manually inserted totals.

🧪 Practical Interview Challenge

Q1. Total sales by region → Region → Rows, Sales → Values

Q2. Average profit by category → Category → Rows, Profit → Values → Average

Q3. Monthly sales trend → Order Date → Rows, Sales → Values, Group by Months

Q4. Top 10 products by sales → Product → Rows, Sales → Values, Value Filters → Top 10

Q5. Interactive regional report → PivotTable + PivotChart + Region Slicer

🎯 Mini Project: Build an Excel Sales Analysis Dashboard

KPIs: Total Sales, Total Profit, Total Orders, Average Order Value

Analysis:

📊 Sales by Region,

📈 Monthly Trend,

📊 Sales by Category,

🏆 Top 10 Products,

📊 Profit by Region

Interactive Controls: Slicers for Region, Category, Year

Double Tap ❤️ For Part-10

𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗮𝗻𝗱 𝗟𝗶𝗻𝗸𝗲𝗱𝗜𝗻 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀🎓

Want to strengthen your resume with career-focused professional skills? Explore these free learning paths from Microsoft and LinkedIn.

🔥 Courses Available:
📌 Project Management
📊 Business Analysis
💻 System Administration
📈 Data Analysis

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlinks.in/micrlink

💡 Learn → Get Certified → Upgrade Your Resume → Boost Your Career

𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

Explore Google Cloud learning resources covering AI/ML fundamentals through practical and advanced concepts.

🚀 Learn AI → Practice ML → Build Skills → Become Career Ready

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlinks.in/eb6

🚀 Learn AI → Practice ML → Build Skills → Become Career Ready

🚀 Data Analyst Roadmap — Part 10

🧹 Excel — Level 9: Power Query for Data Cleaning & Transformation

So far, you've learned how to analyze data using Excel formulas and PivotTables.

But there's a major problem with real-world data:

The data is often messy.

You might receive a monthly Excel file with:

• Duplicate records

• Missing values

• Incorrect data types

• Extra spaces

• Inconsistent names

• Multiple files

• Unnecessary columns

• Data spread across different tables

Cleaning this manually every time is slow and error-prone.

That's where Power Query comes in.

1️⃣ What Is Power Query?

Power Query is a data preparation and transformation tool available in Excel and Power BI.

It allows you to: Connect → Extract → Transform → Load - This is commonly called ETL.

Extract: Get data from a source.

Transform: Clean and reshape the data.

Load: Bring the prepared data into Excel for analysis.

The biggest advantage is repeatability. Instead of cleaning the same file manually every month, you create a transformation process once and refresh it.

2️⃣ Why Should a Data Analyst Learn Power Query?

Imagine your company sends you this file every month:

January.xlsx, February.xlsx, March.xlsx, April.xlsx...

Every file contains 50,000 rows, extra spaces, duplicates, incorrect date formats.

Without Power Query, you repeat the same cleaning every month.

With Power Query: Refresh → Transformations run again

3️⃣ Where Do You Find Power Query?

In modern Excel: Data → Get & Transform Data

Options: From Table/Range, From Workbook, From Text/CSV, From Folder, From Web, From Database

4️⃣ Understand the Power Query Workflow

Data Source → Connect → Power Query Editor → Clean → Transform → Validate → Load → Excel / Data Model → Analysis

Power Query records the transformation steps.

5️⃣ Import Data from Excel & CSV

Excel: Data → Get Data → From File → From Excel Workbook → Select sheet → Open in Power Query Editor

CSV: Data → From Text/CSV → Preview delimiter, headers, data types → Transform Data

6️⃣ Power Query Editor

Left side: Queries

Middle: Data preview

Right side: Applied Steps

Example Applied Steps:

Source → Changed Type → Removed Columns → Filtered Rows → Removed Duplicates → Renamed Columns → Added Custom Column

7️⃣ Changing Data Types

Correct data types are critical.

Order ID → Whole Number, Order Date → Date, Sales → Decimal Number, Customer → Text

Use the data-type icon to change it.

8️⃣ Remove Duplicates

If Order ID should be unique, select the column and use: Remove Rows → Remove Duplicates

🔟 Important: Understand What a Duplicate Means

Don't automatically delete duplicates.

Ask: > Is this actually a duplicate?

Two records with same customer but different orders = Not a duplicate.

Same order appearing twice = Duplicate.

1️⃣1️⃣ Remove & Rename Columns

Remove unnecessary columns: Home → Remove Columns

Rename for clarity: CustNm → Customer Name, SlsAmt → Sales

1️⃣2️⃣ Filter Rows

Filtering in Power Query becomes part of the reusable query.

Example: Keep only orders from 2026, or North region, or Sales > 0

1️⃣3️⃣ Handle Missing Values

Never blindly replace missing values with zero.

A missing salary doesn't mean Salary = 0, it means it wasn't provided.

1️⃣4️⃣ Replace Values

Standardize inconsistent entries:

North, NORTH, north, N → North

Use: Replace Values

1️⃣5️⃣ Trim and Clean Text

Transform " John Smith " → "John Smith"

Trim whitespace, Clean non-printing characters, Change case

1️⃣6️⃣ Split Columns

John-Smith → First Name: John, Last Name: Smith

Use: Split Column → By Delimiter → "-"

1️⃣7️⃣ Merge Columns

John + Smith → John Smith

Use: Merge Columns with space separator

2️⃣0️⃣ Add Custom Columns

Sales: 100,000, Cost: 70,000 → Profit = Sales - Cost = 30,000

Profit Margin = Profit / Sales

2️⃣1️⃣ Conditional Columns

IF Sales >= 100000 THEN "High" ELSE IF Sales >= 50000 THEN "Medium" ELSE "Low"

Similar to Excel's IF()

2️⃣2️⃣ Merge Queries (The most important concept)

Sales: Product ID, Sales

Products: Product ID, Product, Category

Use: Merge Queries → Match Product ID → This is like a JOIN in SQL.

SQL: SELECT * FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;

2️⃣3️⃣ Append Queries

Merge = Add columns by matching keys

Append = Add rows by stacking

Jan (1001, 1002) + Feb (1003, 1004) → 1001, 1002, 1003, 1004

2️⃣4️⃣ Group By

Region: North 50K, North 70K → Group by Region, Sum Sales → North 120K

Similar to SQL GROUP BY

2️⃣5️⃣ Pivot and Unpivot

This is critical for reports designed for humans:

Before:

Region | Jan | Feb | Mar

North | 50K | 60K | 70K

After Unpivot:

Region | Month | Sales

North | Jan | 50K

North | Feb | 60K

This structure is much better for analysis.

2️⃣6️⃣ Applied Steps = Your Superpower

Source → Changed Type → Removed Columns → Trimmed Text → Removed Duplicates → Filtered Rows → Added Profit → Merged Products

When new data arrives, just Refresh.

🧪 Practical Interview Challenge

Messy file with: Duplicate Order IDs, Extra spaces, Sales as text, Missing regions, Product info in another file

Strong approach:

1. Import into Power Query

2. Set correct data types

3. Trim and clean text

4. Investigate duplicates

5. Handle missing regions per business rules

6. Merge Product lookup table

7. Add Profit column

8. Filter invalid records

9. Review Applied Steps

10. Load cleaned dataset

🏆 Key Lesson

Instead of: > "How do I clean this file?"

Think: > "How do I build a repeatable process that cleans this type of data every time?"

That's the difference between manually manipulating spreadsheets and building a professional analytics workflow.

Remember:

Merge = Add columns by matching data

Append = Add rows

Group By = Summarize

Unpivot = Convert columns into rows

Applied Steps = Record your process

Refresh = Run the process again

Double Tap ❤️ For Part-11

🚀 Data Analyst Roadmap — Part 11

🗄️ SQL — Level 1: SQL Fundamentals & Databases

You've completed the major Excel section of the roadmap.

Now we're moving to one of the most important skills for a Data Analyst: SQL

If Excel helps you analyze spreadsheet-based data, SQL helps you work directly with data stored in databases.

A Data Analyst should be able to use SQL to:

• Retrieve data

• Filter records

• Sort results

• Summarize information

• Join tables

• Find trends

• Calculate KPIs

• Investigate business problems

1️⃣ What Is SQL?

SQL stands for: Structured Query Language

It's a language used to communicate with relational databases.

For example, suppose a company stores millions of sales records in a database.

Instead of opening a huge spreadsheet, you can ask the database:

"Give me all sales from the North region."

Or:

"What was total revenue last month?"

Or:

"Which 10 products generated the most revenue?"

SQL allows you to ask these questions directly.

2️⃣ Why Is SQL Important for Data Analysts?

Imagine a company has:

50 million transactions.

Excel isn't the right tool for storing and querying all that information.

The data may be stored in a database such as:

• PostgreSQL

• MySQL

• Microsoft SQL Server

• Oracle Database

• Snowflake

• BigQuery

As a Data Analyst, you may connect to the database and use SQL to extract the data you need.

A typical workflow looks like:

Database

↓

SQL Query

↓

Required Data

↓

Analysis

↓

Dashboard / Report

↓

Business Decision

3️⃣ What Is a Database?

A database is a system used to store and manage data.

For example, an e-commerce company might have:

• Customers

• Products

• Orders

• Payments

• Employees

Each represents a different type of information.

Instead of putting everything into one enormous table, relational databases typically organize related information into separate tables.

4️⃣ What Is a Table?

A table is a structured collection of data organized into:

Rows + Columns

For example:

Customers

Customer_ID Customer_Name City

101 John Mumbai

102 Sarah Pune

103 Mike Delhi

Each row represents one customer.

Each column represents an attribute.

This should look familiar from Excel.

5️⃣ Rows vs Columns

Just like Excel:

Row

Represents a record.

Example:

101 | John | Mumbai

represents one customer.

Column

Represents an attribute.

For example:

• Customer_ID

• Customer_Name

• City

A useful rule:

One row = one record

One column = one attribute

6️⃣ What Is a Primary Key?

A Primary Key uniquely identifies each record in a table.

For example:

Customer_ID Customer_Name

101 John

102 Sarah

103 Mike

Here:

Customer_ID

can be the primary key.

Each customer should have a unique ID.

101 → John

102 → Sarah

103 → Mike

You shouldn't have two different customers with the same primary key.

7️⃣ What Is a Foreign Key?

A Foreign Key is a column used to establish a relationship between tables.

Suppose:

Customers

Customer_ID Customer_Name

101 John

102 Sarah

Orders

Order_ID Customer_ID Sales

5001 101 50,000

5002 102 70,000

5003 101 30,000

Here:

Customers.Customer_ID

is the primary key.

Orders.Customer_ID

can be a foreign key.

This allows us to connect orders to customers.

8️⃣ Understanding Relationships

The relationship is:

Customers

Customer_ID

↓

Orders

One customer can have multiple orders.

For example:

John

↓

Order 5001

Order 5003

Order 5010

This is a:

One-to-Many relationship

It's one of the most important database concepts for Data Analysts.

9️⃣ What Is a Relational Database?

A relational database stores data in related tables.

For example:

Customers

↓

Orders

↓

Order Details

↓

Products

Instead of storing the customer's name repeatedly in every order, the database can store:

Customer_ID

and retrieve the customer information through relationships.

This helps reduce unnecessary duplication.

🔟 What Is SQL Syntax?

SQL queries generally consist of keywords and expressions.

For example:

SELECT *

FROM Customers;

This asks:

Return all columns from the Customers table.

Let me break it down.

SELECT: Specifies what you want to retrieve.

FROM: Specifies the table.

Customers: The table you're querying.

1️⃣1️⃣ SELECT

SELECT is one of the first SQL commands you need to learn.

Suppose you have:

Employees

Employee_ID Name Department Salary

101 John IT 75,000

102 Sarah HR 60,000

103 Mike Finance 82,000

To retrieve all columns:

SELECT *

FROM Employees;

1️⃣2️⃣ Selecting Specific Columns

You don't always need every column.

Suppose you only want:

Name and Department

Use:

SELECT Name, Department

FROM Employees;

Result:

Name Department

John IT

Sarah HR

Mike Finance

This is generally better than using SELECT * when you only need specific fields.

1️⃣3️⃣ Why Avoid SELECT * in Production Queries?

You may see beginners writing:

SELECT *

FROM Employees;

all the time.

It's useful while learning and exploring data.

But in production queries, explicitly selecting the required columns is often better because:

• It makes the query clearer

• It avoids retrieving unnecessary data

• It can reduce data transfer

• It makes downstream dependencies more predictable

For example:

SELECT Employee_ID, Name, Salary

FROM Employees;

is more intentional.

1️⃣4️⃣ WHERE

WHERE filters records.

Suppose you want employees from IT.

SELECT *

FROM Employees

WHERE Department = 'IT';

Result:

Employee_ID Name Department Salary

101 John IT 75,000

The database only returns records satisfying the condition.

1️⃣5️⃣ Filtering Numeric Values

Suppose you want employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Salary > 70000;

Result:

Employee_ID Name Department Salary

101 John IT 75,000

103 Mike Finance 82,000

1️⃣6️⃣ Comparison Operators

You should know these operators:

Operator Meaning

= Equal to

<> Not equal to

Greater than

< Less than

= Greater than or equal

<= Less than or equal

Examples:

WHERE Salary >= 80000

WHERE Department <> 'HR'

1️⃣7️⃣ AND

AND requires all conditions to be true.

Suppose you want:

IT employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Department = 'IT'

AND Salary > 70000;

The record must satisfy both conditions.

Think:

IT

AND

Salary > 70,000

1️⃣8️⃣ OR

OR requires at least one condition to be true.

Suppose you want:

IT or Finance employees.

SELECT *

FROM Employees

WHERE Department = 'IT'

OR Department = 'Finance';

Both departments will be included.

1️⃣9️⃣ IN

When checking multiple values, IN makes your query cleaner.

Instead of:

WHERE Department = 'IT'

OR Department = 'Finance'

OR Department = 'HR'

you can write:

WHERE Department IN ('IT', 'Finance', 'HR');

This is easier to read and maintain.

2️⃣0️⃣ NOT IN

You can exclude multiple values.

SELECT *

FROM Employees

WHERE Department NOT IN ('HR', 'Finance');

This returns employees who aren't in those departments.

2️⃣1️⃣ BETWEEN

BETWEEN checks whether a value falls within a range.

For example:

SELECT *

FROM Employees

WHERE Salary BETWEEN 50000 AND 80000;

This returns salaries within the specified range.

For numeric data, this is often useful for:

• Salary ranges

• Sales ranges

• Age ranges

• Scores

• Transaction values

2️⃣2️⃣ LIKE

LIKE is used for pattern matching.

Suppose you want employees whose names start with J.

SELECT *

FROM Employees

WHERE Name LIKE 'J%';

% means:

Any number of characters.

So this could match:

• John

• James

• Jennifer

2️⃣3️⃣ LIKE with Wildcards

•

Starts with J

LIKE 'J%'

•

Ends with n

LIKE '%n'

•

Contains "oh"

LIKE '%oh%'

Wildcards are extremely useful when searching text data.

2️⃣4️⃣ DISTINCT

DISTINCT removes duplicate values from the result.

Suppose your employee table contains:

• IT

• HR

• IT

• Finance

• HR

• IT

Use:

SELECT DISTINCT Department

FROM Employees;

Result:

IT

HR

Finance

This is useful for discovering categories in a dataset.

2️⃣5️⃣ ORDER BY

ORDER BY sorts your results.

Suppose you want employees with the highest salary first.

SELECT *

FROM Employees

ORDER BY Salary DESC;

DESC means:

Descending

Highest → Lowest

2️⃣6️⃣ ASC

ASC means ascending.

SELECT *

FROM Employees

ORDER BY Salary ASC;

Lowest → Highest

Ascending is generally the default sort direction.

2️⃣7️⃣ LIMIT / TOP

The syntax depends on the database system.

In systems such as PostgreSQL and MySQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 5;

This returns the top 5 employees by salary.

In SQL Server, you would commonly use:

SELECT TOP 5 *

FROM Employees

ORDER BY Salary DESC;

This is an important point:

SQL is a language, but different database systems have slightly different syntax.

2️⃣8️⃣ Aliases

Aliases give columns or tables temporary names within a query.

For example:

SELECT

Name AS Employee_Name,

Salary AS Annual_Salary

FROM Employees;

The result displays:

Employee_Name Annual_Salary

John 75,000

Sarah 60,000

Aliases make results easier to understand.

2️⃣9️⃣ SQL Comments

You can add comments to explain your queries.

For example:

-- Get employees earning more than 70,000

SELECT Name, Salary

FROM Employees

WHERE Salary > 70000;

Comments don't affect the query result.

They're useful when queries become complex.

🧪 Practical Interview Challenge

Suppose you have:

Employees

ID Name Department Salary

101 John IT 75,000

102 Sarah HR 60,000

103 Mike Finance 82,000

104 David IT 90,000

105 Alice HR 65,000

Q1. Retrieve all employees.

SELECT *

FROM Employees;

Q2. Retrieve only names and salaries.

SELECT Name, Salary

FROM Employees;

Q3. Find employees earning more than ₹70,000.

SELECT *

FROM Employees

WHERE Salary > 70000;

Q4. Find IT employees.

SELECT *

FROM Employees

WHERE Department = 'IT';

Q5. Find IT or Finance employees.

SELECT *

FROM Employees

WHERE Department IN ('IT', 'Finance');

Q6. Sort employees by salary from highest to lowest.

SELECT *

FROM Employees

ORDER BY Salary DESC;

Q7. Find the top 3 highest-paid employees.

PostgreSQL/MySQL:

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 3;

SQL Server:

SELECT TOP 3 *

FROM Employees

ORDER BY Salary DESC;

Q8. List unique departments.

SELECT DISTINCT Department

FROM Employees;

🏆 Double Tap ❤️ For More

🚀 Data Analyst Roadmap — Part 12

🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING

Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.

This is one of the most important SQL skills for Data Analysts.

In real interviews and jobs, you'll frequently be asked questions like:

What is the total sales by region?

What is the average salary by department?

How many customers are in each city?

Which products generated more than ₹10 lakh in sales?

To answer these questions, you need:

Aggregate Functions + GROUP BY + HAVING

1️⃣ What Are Aggregate Functions?

Aggregate functions perform calculations across multiple rows and return a summarized result.

The most important ones are:

SUM()

COUNT()

AVG()

MIN()

MAX()

Think of them as the SQL equivalent of the basic Excel functions you learned earlier.

2️⃣ SUM()

SUM() calculates the total of a numeric column.

Suppose you have:

Order_ID: 1001, Sales: 50,000

Order_ID: 1002, Sales: 70,000

Order_ID: 1003, Sales: 30,000

Query:

SELECT SUM(Sales) AS Total_Sales
FROM Orders;

Result:

Total_Sales = 150,000

Business question

What is our total revenue?

Answer → SUM()

3️⃣ COUNT()

COUNT() counts records.

SELECT COUNT(*) AS Total_Orders
FROM Orders;

If there are 10,000 orders:

Total_Orders = 10,000

Why COUNT(*)?

COUNT(*) counts rows.

This is often useful when you want the total number of records.

4️⃣ COUNT(Column)

You can also count values in a specific column.

SELECT COUNT(Customer_ID) AS Customer_Count
FROM Orders;

One important distinction:

COUNT(column) generally doesn't count NULL values.

Whereas:

COUNT(*)

counts rows regardless of whether individual columns contain NULLs.

5️⃣ COUNT(DISTINCT)

Suppose your Orders table contains:

Order 1001 → Customer 101

Order 1002 → Customer 102

Order 1003 → Customer 101

Order 1004 → Customer 103

There are:

4 orders

but only:

3 unique customers

Use:

SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;

Result:

3

This is extremely important in analytics.

6️⃣ AVG()

AVG() calculates the average.

Suppose salaries are:

50,000, 60,000, 70,000

Query:

SELECT AVG(Salary) AS Average_Salary
FROM Employees;

Result:

60,000

Business questions

What is the average order value?

What is the average employee salary?

What is the average product price?

Answer → AVG()

7️⃣ MIN()

MIN() returns the smallest value.

SELECT MIN(Salary) AS Minimum_Salary
FROM Employees;

Example result:

35,000

Useful for:

Minimum salary

Lowest sales

Earliest date

Lowest transaction value

8️⃣ MAX()

MAX() returns the largest value.

SELECT MAX(Salary) AS Maximum_Salary
FROM Employees;

Result:

150,000

Useful for:

Highest salary

Highest sales

Largest transaction

Latest date

9️⃣ Using Multiple Aggregate Functions

You can use several aggregate functions in one query.

SELECT
    SUM(Sales) AS Total_Sales,
    AVG(Sales) AS Average_Sales,
    MIN(Sales) AS Minimum_Sales,
    MAX(Sales) AS Maximum_Sales,
    COUNT(*) AS Total_Orders
FROM Orders;

The process is:

WHERE → Filter rows

GROUP BY → Create groups

SUM → Calculate totals

HAVING → Filter groups

This sequence is fundamental to SQL analysis.

1️⃣9️⃣ ORDER BY with GROUP BY

You can sort aggregated results.

Suppose you want regions with the highest sales first:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC;

Result:

North: 500,000, South: 350,000, West: 200,000, East: 150,000

2️⃣0️⃣ Top 3 Regions

You can combine:

GROUP BY + ORDER BY + LIMIT

For example, in PostgreSQL/MySQL:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC
LIMIT 3;

This answers:

"Which three regions generated the most sales?"

2️⃣1️⃣ GROUP BY Dates

Suppose you have:

Order_Date and Sales

You might want:

Total sales by year.

The exact date function varies by database system.

For example, in PostgreSQL:

SELECT
    EXTRACT(YEAR FROM Order_Date) AS Sales_Year,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Sales_Year;

Result:

2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000

2️⃣2️⃣ Grouping by Month

In PostgreSQL, you can use:

SELECT
    DATE_TRUNC('month', Order_Date) AS Sales_Month,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Sales_Month;

This creates monthly sales totals.

Different SQL platforms have different date functions, so always check the database you're working with.

2️⃣3️⃣ Calculate Average Order Value

A common business KPI is:

Average Order Value (AOV)

A simple version is:

SELECT
    SUM(Sales) / COUNT(*) AS Average_Order_Value
FROM Orders;

If each row represents exactly one order.

If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:

SELECT
    SUM(Sales) / COUNT(DISTINCT Order_ID) AS Average_Order_Value
FROM Orders;

This distinction is extremely important.

2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics

Suppose a customer places multiple orders:

Customer 101 → Orders 5001, 5002

Customer 102 → Order 5003

Customer 103 → Orders 5004, 5005

Total orders: 5

Unique customers: 3

Query:

SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;

Result: 3

This is commonly used for metrics such as:

Active customers

Unique users

Unique accounts

Distinct orders

Distinct products

2️⃣5️⃣ Conditional Aggregation

One powerful technique is combining CASE WHEN with aggregate functions.

For example:

Count how many orders were above ₹50,000.

SELECT
    SUM(
        CASE
            WHEN Sales > 50000 THEN 1
            ELSE 0
        END
    ) AS High_Value_Orders
FROM Orders;

This allows you to create customized metrics.

You'll use this technique much more in advanced SQL.

2️⃣6️⃣ Common SQL Analytical Pattern

A very common query structure is:

SELECT
    Dimension,
    AGGREGATE_FUNCTION(Metric) AS KPI
FROM Table
WHERE Condition
GROUP BY Dimension
HAVING Aggregate_Condition
ORDER BY KPI DESC;

For example:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;

This produces a compact business summary.

For example:

Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200

🔟 Why Do We Need GROUP BY?

Aggregate functions give you an overall summary.

But what if the business asks:

"What are total sales for each region?"

You need to divide the data into groups.

That's what GROUP BY does.

1️⃣1️⃣ Basic GROUP BY

Suppose:

North: 50,000 and 70,000 → Total 120,000

South: 40,000 and 60,000 → Total 100,000

West: 80,000 → Total 80,000

Query:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region;

Result:

North = 120,000, South = 100,000, West = 80,000

Now you've answered:

"How much did each region sell?"

1️⃣2️⃣ GROUP BY Department

Suppose you have:

John - IT - 75,000

Sarah - HR - 60,000

Mike - IT - 82,000

David - Finance - 90,000

Alice - HR - 65,000

Query:

SELECT
    Department,
    AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department;

Result:

Finance: 90,000, HR: 62,500, IT: 78,500

1️⃣3️⃣ GROUP BY with COUNT()

Question:

How many employees are in each department?

SELECT
    Department,
    COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department;

Result:

IT: 2, HR: 2, Finance: 1

1️⃣4️⃣ GROUP BY with Multiple Columns

You can group by more than one column.

Suppose your sales data contains:

North Electronics: 80,000

North Furniture: 40,000

South Electronics: 70,000

South Furniture: 50,000

Query:

SELECT
    Region,
    Category,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region, Category;

Result:

North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000

This lets you analyze combinations of dimensions.

1️⃣5️⃣ GROUP BY vs PivotTable

This is an important connection.

In Excel:

Region → Rows

Sales → Values

In SQL:

SELECT
    Region,
    SUM(Sales)
FROM Orders
GROUP BY Region;

The analytical concept is very similar.

You're grouping records and calculating an aggregate.

1️⃣6️⃣ HAVING

Now suppose you want:

"Show only regions where total sales are greater than ₹100,000."

You can't simply use WHERE on the aggregate result.

You use: HAVING

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;

Result:

North = 120,000

1️⃣7️⃣ WHERE vs HAVING

This is a very common SQL interview question.

WHERE

Filters individual rows before grouping.

Example:

SELECT *
FROM Orders
WHERE Region = 'North';

HAVING

Filters groups after aggregation.

Example:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;

Remember:

WHERE → Filter rows

HAVING → Filter groups

1️⃣8️⃣ WHERE + GROUP BY + HAVING

You can use all three.

Question:

Find regions where 2026 sales exceed ₹100,000.

Conceptually:

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
  AND Order_Date < '2027-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000;

Learning this structure will make many analytical SQL problems much easier.

🧪 Practical Interview Challenge

Suppose you have Orders with:

1001 North Electronics 80,000

1002 North Furniture 40,000

1003 South Electronics 70,000

1004 South Furniture 50,000

1005 North Electronics 60,000

Q1. Find total sales.

SELECT SUM(Sales) AS Total_Sales FROM Orders;

Q2. Find average sales.

SELECT AVG(Sales) AS Average_Sales FROM Orders;

Q3. Find sales by region.

SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region;

Q4. Count orders by region.

SELECT Region, COUNT(*) AS Order_Count FROM Orders GROUP BY Region;

Q5. Find average sales by category.

SELECT Category, AVG(Sales) AS Average_Sales FROM Orders GROUP BY Category;

Q6. Show only regions with sales greater than ₹100,000.

SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region HAVING SUM(Sales) > 100000;

Q7. Sort regions by highest sales.

SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region ORDER BY Total_Sales DESC;

🏆 Double Tap ❤️ For More

𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝘁𝗼 𝗙𝘂𝘁𝘂𝗿𝗲-𝗣𝗿𝗼𝗼𝗳 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 😍

🔥 Skills Worth Learning:

⛓️ Blockchain
☁️ Cloud Computing
♾️ DevOps Engineering
🤖 Artificial Intelligence & Machine Learning
📊 Data Science & Analytics
🔐 Cybersecurity
🎯 Leadership & Communication

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:-

https://pdlinks.in/i89

Don’t just collect certificates — build projects, gain practical experience and showcase your skills on your resume & LinkedIn.