4. GroupBy in SQL

Aggregate Functions

An aggregate function returns a single result row, based on a group of rows in a Table.

Count

Count returns the number of rows returned by the query. If asterisk (*) is specified, then this function returns all rows, including duplicates and nulls. COUNT never returns null.

Syntax:

                  Count (*[DISTINCT] All/Column name)

Ques. Display the number of Employees working in the Organization.

                        SELECT COUNT(*)

                        FROM emp;

---

Ques. Count distinct Managers in the Employees table.

SELECT COUNT (Distinct MGR) "Managers" 

FROM emp;

---

Ques. Display all the jobs present in emp table

SELECT job 

FROM emp;

---

Sum ()

The Sum function returns the Sum of values of the select list of columns.

Syntax:

            Sum(Distinct /ALL/Column name)

Ques. List the total salaries payable to employees.

SELECT Sum(Sal)

FROM emp;

---

Avg( )

The AVG function returns the Average of values of column values.

Ques. Calculate the Average of all the Salaries in the emp table.

SELECT Avg(Sal)

FROM emp;

---

Grouping the result of a query

• The GROUP BY clause is used to divide the rows in a table into smaller groups
• The GROUP BY is used with the SELECT statement
• SQL groups the result after it retrieves the rows from the table
• Conditional retrieval of rows from a grouped result is possible with HAVING clause
• ORDER BY can be used to order the final result

Syntax:

SELECT <Column-list> FROM <table-name>

WHERE <condition>

GROUP BY <column-name(s)>

HAVING <condition>

ORDER BY <expression>;

• Group By Clause
  • The GROUP BY clause is used in a SELECT statement to collect/combine data across multiple records and group the results by one or more columns.
  • The GROUP BY clause is used with aggregate functions such as SUM (), AVG (), COUNT (), MAX (), and MIN ().
  • Aggregate functions are used to return summary information for each group. The aggregate functions are applied to individual groups.
  • The columns or expressions that are not encapsulated within an aggregate function, must be included in the GROUP BY clause.
  • In the query, Group by appears after From

The syntax of Group by clause is:

               SELECT <Column1, .. Column n> 

                FROM <Table name1, Table name2..Table name n> 

               Group By ColumnsList;

Ques. List the department and number of employees working in each department.

                   SELECT DeptNo, Count (*) 

                   FROM emp 

                   GROUP BY DeptNo;

Practice: Write the above query without GROUP BY (last line) and discuss the result.

---

 Ques. List the department number and the total salary payable in each department

                SELECT deptno, Sum(Sal) 

               FROM emp

               GROUP BY deptno; 

---

 Ques. Find the minimum salary in each department

 SELECT deptno, MIN(Sal) AS "Lowest salary" 

FROM emp

GROUP BY deptno;

Restrict the groups returned by Group By clause – Having clause

Having clause is used to specify which groups are to be displayed i.e. restrict the groups that you return on the basis of aggregate functions. Having is similar to Where Clause but used to restrict the Groups. 

Ques. List total salary for all the departments employing more than five people

SELECT deptno, Sum (Sal), count(empno)

FROM emp

GROUP BY deptno

HAVING count (*) > 5;

---

Order of execution

The above clauses are executed in the following order:

1. Restrict the individual rows using WHERE
2. Group the returned rows based on the GROUP BY
3. Restrict the Grouped rows by using the HAVING
4. Sort the result using the ORDER BY clause. Order By must use the aggregate function or the column in the group by clause.