Orderby,Groupby,having

ORDERBY

clause is used to sort the result set of a query based on one or more columns. You can specify the sorting order as ascending (ASC) or descending (DESC).

Example Query:  SELECT student_id, name, score FROM students  ORDER BY score DESC;

Sample Table with data

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, total_amount)

VALUES
(1, 101, '2023-10-16', 125.50),
(2, 102, '2023-10-17', 230.75),
(3, 101, '2023-10-17', 75.25),
(4, 103, '2023-10-18', 150.00),
(5, 102, '2023-10-18', 99.99);

Example Query:  SELECT customer_id, order_date, total_amount FROM orders ORDER BY total_amount DESC;

Output:

customer_id	order_date	total_amount

102

2023-10-17

230.75

4

2023-10-18

150.00

1

2023-10-16

125.50

2

2023-10-18

99.99

3

2023-10-17

75.25

The rows are sorted in descending order based on the total_amount column.

_____________________________________________________

GROUPBY

clause is used to group rows that have the same values in specified columns into summary rows, typically with an aggregate function applied.

Example Query-1:  SELECT customer_id, SUM(total_amount) as total_order_amount FROM orders GROUP BY customer_id;

Output:

customer_id	total_order_amount

101

200.75

102

330.74

103

150.00

Example Query-2:  SELECT customer_id, SUM(total_amount) FROM orders GROUP BY customer_id;

Output:

customer_id	total_order_amount

101

200.75

102

330.74

103

150.00

Explanation:

• customer_id = 101: $125.50 + 75.25 = 200.75$
• customer_id = 102: $230.75 + 99.99 = 330.74$
• customer_id = 103: $150.00$ (only one order)

__________________________________________________________________

Sample table with data

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
job_title VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'John', 'Doe', 'IT', 'Developer', 70000.00),
(2, 'Jane', 'Smith', 'HR', 'HR Specialist', 60000.00),
(3, 'Bob', 'Johnson', 'IT', 'System Analyst', 80000.00),
(4, 'Alice', 'Williams', 'Finance', 'Accountant', 75000.00),
(5, 'Charlie', 'Brown', 'Marketing', 'Marketing Coordinator', 65000.00),
(6, 'Eva', 'Davis', 'HR', 'Recruiter', 70000.00),
(7, 'Frank', 'Miller', 'Finance', 'Financial Analyst', 82000.00),
(8, 'Grace', 'Taylor', 'Marketing', 'Marketing Manager', 90000.00);

Example Queries:

         Basic GROUP BY:

SELECT department, COUNT(*) as employee_count

FROM employees

GROUP BY department;

Expected Output:

department	employee_count
IT	2
HR	2
Finance	2
Marketing	2

Explanation:

• IT department has 2 employees (John, Bob).
• HR department has 2 employees (Jane, Eva).
• Finance department has 2 employees (Alice, Frank).
• Marketing department has 2 employees (Charlie, Grace).

The COUNT(*) function counts the number of employees in each department.

------------------------------------------------------------------

GROUP BY with ORDER BY:

SELECT department, COUNT(*) as employee_count

FROM employees

GROUP BY department

ORDER BY employee_count DESC;

Expected Output (Sorted by Employee Count Descending):

department	employee_count
IT	2
HR	2
Finance	2
Marketing	2

Explanation:

• Each department has 2 employees.
• Since all department counts are the same (2), the ORDER BY employee_count DESC does not change the order from the previous result.
• The result remains grouped by department and sorted by count in descending order, but since all counts are equal, the order remains unchanged.

---------------------------------------------------------------------------

GROUP BY with multiple columns:

SELECT department, job_title, sum(salary) as avg_salary

FROM employees

GROUP BY department, job_title

ORDER BY department, avg_salary DESC;

Expected Output :

department	job_title	total_salary
Finance	Financial Analyst	82000.00
Finance	Accountant	75000.00
HR	Recruiter	70000.00
HR	HR Specialist	60000.00
IT	System Analyst	80000.00
IT	Developer	70000.00
Marketing	Marketing Manager	90000.00
Marketing	Marketing Coordinator	65000.00

---

Explanation:

1. The results are grouped by department and job_title.
2. The SUM(salary) is applied, but since each job title has only one employee, it's just the salary value itself.
3. The ORDER BY department, total_salary DESC ensures:
   • Departments appear alphabetically.
   • Within each department, job titles are sorted by salary in descending order.

---------------------------------------------------------------------------------------------

HAVING Clause:

HAVING filters records that work on summarized GROUP BY results. 

HAVING applies to summarized group records, whereas WHERE applies to individual records. 

Only the groups that meet the HAVING criteria will be returned.

HAVING requires that a GROUP BY clause is present.

WHERE and HAVING can be in the same query

Example Query: 

GROUP BY with HAVING:

SELECT department, COUNT(*) as employee_count

FROM employees

GROUP BY department

HAVING employee_count <5;

Expected Output:

department	employee_count
IT	2
HR	2
Finance	2
Marketing	2

---

Explanation:

• The HAVING clause filters only those departments where the count of employees is less than 5.
• Since all departments have 2 employees, they all meet the condition and appear in the result.

---------------------------------------------------------------------------------------------

Filtering using HAVING and ORDER BY together:

SELECT department, job_title, AVG(salary) as avg_salary

FROM employees

GROUP BY department, job_title

HAVING avg_salary > 50000

ORDER BY avg_salary DESC;

Expected Output:

department	job_title	avg_salary
Marketing	Marketing Manager	90000.00
Finance	Financial Analyst	82000.00
IT	System Analyst	80000.00
Finance	Accountant	75000.00
IT	Developer	70000.00
HR	Recruiter	70000.00
Marketing	Marketing Coordinator	65000.00
HR	HR Specialist	60000.00

---

Explanation:

• Filtering using HAVING:
  • Since all salaries are above 50,000, no records are removed.
• Sorting using ORDER BY avg_salary DESC:
  • Highest salary comes first (Marketing Manager with 90,000)
  • Lowest salary comes last (HR Specialist with 60,000).