SQL – Order By, Aggregate Functions and Group By
with/without Having
SQL - ORDER BY
• The ORDER BY clause sorts the results of a query
• You can sort in ascending (default) or descending order
• Multiple columns can be given
• You cannot order by a column which isn’t in the result
SELECT <columns> FROM <tables> WHERE <condition>
ORDER BY <cols> [ ASC | DESC ]
SQL - ORDER BY Example
Table - Grades SELECT * FROM Grades ORDER BY Mark
Name Code Mark Name Code Mark
John DBS 56 Mark PR2 35
John IAI 72 Mark PR1 43
Mary DBS 60 Rohan IAI 54
Mark PR1 43 John DBS 56
Mark PR2 35 Mary DBS 60
Rohan IAI 54 John IAI 72
SQL - ORDER BY Example
SELECT * FROM Grades ORDER BY Code ASC,
Mark DESC
Table - Grades
Name Code Mark Name Code Mark
John DBS 56 Mary DBS 60
John IAI 72 John DBS 56
Mary DBS 60 John IAI 72
Mark PR1 43 Rohan IAI 54
Mark PR2 35 Mark PR1 43
Rohan IAI 54 Mark PR2 35
SQL - Aggregate Functions
SQL - Aggregate Functions
• Aggregate functions compute summaries of data in a table
• Most aggregate functions (all except COUNT) work on a single column of numeric data
• Use an alias to name the result
SQL Aggregate functions :
COUNT: The number of rows
SUM: The sum of the entries in a column
AVG: The average entry in a column
MIN, MAX: The minimum and maximum entries in a column
SQL - Aggregate Functions
Grades SELECT COUNT(*)FROM Grades Count(Name)
Name Code Mark 6
John DBS 56
John IAI 72
Mary DBS 60
NULL PR1 43 SELECT Avg(Mark) AS Total FROM Grades
Mark PR2 35 Total
Rohan IAI 54
320
SELECT MAX(Mark) AS “Best” FROM Grades
Best
72
SQL - Aggregate Functions
• We can combine aggregate functions SELECT MAX(Mark)- MIN(Mark)
using arithmetic AS “Range” FROM Grades
Grades
Name Code Mark
John DBS 56
John IAI 72
MAX(Mark) = 72 Range
Mary DBS 60
37
Mark PR1 43
Mark PR2 35 MIN(Mark) = 35
Rohan IAI 54
SQL - Aggregate Functions
Count () and Count (*)
SQL - GROUP BY
• SQL allows us to apply aggregate functions on grouped records as certain
information can only be generated by grouping records.
• Grouping is always done along with one of the aggregate functions.
• Example, find the average mark of each student. In this example grouping
is performed based on student name as the name will repeat as many
times as the number of subjects.
SQL - GROUP BY
Grouping records based on student name from student_marks table
Records can also be grouped based on subject names
Select SFName, sum(Marks) from student_info
Group By SFNmae
SQL – Group BY
General SQL syntax format
SQL – Group BY
Employee_Table
Select DeptID AVG(Salary) from Employee Group By DeptID
SQL - GROUP BY - Example
Table - Grades
SELECT Name, AVG(Mark) AS Average
Name Code Mark FROM Grades GROUP BY Name
John DBS 56
John IAI 72
Mary DBS 60 Name Average
Tom PR1 43 John 64
Tom PR2 35 Mary 60
Rohan IAI 54 Tom 39
Rohan 54
SQL - GROUP BY
SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees GROUP BY emp_name;
SQL - GROUP BY
Another example
Table Name - Sales
Month Department Value • Find the total value of the sales for each
department in each month
March Finance 20
March Travel 30 • Grouping can be done first by Month then
Department or Department then Month
March Technical 40
April Finance 10 • Same results, but in a different order
April Finance 30
April Travel 25
April Finance 20
May Finance 20
May Technical 50
SQL - GROUP BY
(Grouping done on more than one column)
SELECT Month, Department, SELECT Month, Department,
SUM(Value) AS Total SUM(Value) AS Total
FROM Sales FROM Sales
GROUP BY Month, Department GROUP BY Department, Month
Month Department Total Month Department Total
April Finance 60 April Finance 60
April Travel 25 March Finance 20
March Finance 20 May Finance 20
March Technical 40 March Technical 40
March Travel 30 May Technical 50
May Finance 20 April Travel 25
May Technical 50 March Travel 30
SQL – Group By with HAVING
• HAVING is used with Group by to filter from the grouped record set or to specify a condition
for group by result
• HAVING is like a WHERE clause, except that it applies to the results of a GROUP BY query
• It can be used to select groups which satisfy a given condition
SELECT Name, AVG(Mark) AS Average
FROM Grades GROUP BY Name HAVING AVG(Mark) >= 40
Name Average
John 64
Mary 60
Rohan 54
