Calculations and Groupings (DML)

CONCATenation

While we may want certain fields to be kept separate there are occasions (such as the example shown below) where we may want to stick a couple of fields together (concatenation) and refer to the new field by a new field name.Here the first name and surname fields are concatenated together and referred to as fullname.

SELECT CONCAT(Emp_fname,' ',Emp_sname) AS fullname
FROM employee;

Note: AS is optional

UCASE

If you want a field to appear in uppercase you can do the following:

SELECT Emp_fname, UCASE(Emp_sname)
FROM employee;

Applying it to the concatenation example we could do this:

SELECT CONCAT(Emp_fname,' ',UCASE(Emp_sname)) AS fullname
FROM employee;

Math Operators

You can use the maths operators in conjunction with the fields specified/stated. So, if we wanted to display what the result of a 20% increase in salary (times 1.2) would be on each grade we could write:

SELECT grade, salary, salary*1.2 AS new_salary
FROM pay_structure;

Note: AS is optional

Note: you can also use +, - and /

COUNT

The COUNT function returns the total number of records which contain a particular field.

SELECT Count(Emp_No) AS no_of_employees
FROM employee;

You can also be more specific by using the WHERE operator. Here it is only counting the number of employees are on a grade 5.

SELECT Count(Emp_No) AS no_of_employees
FROM employee
WHERE Grade=’5’;

Note: AS no_of_employees is optional

SUM

The SUM function returns the total value of a field passed to it. So if you wanted to work out the total number of months being spend on all projects you could write:

SELECT SUM(Months_Allocated_To_Project) AS total_man_months
FROM emp_on_project;

MIN

The MIN function returns the smallest value from a series of records. So, to return the project with the least number of months you would write:

SELECT Proj_Code, MIN(Months_Allocated_To_Project)

FROM emp_on_project;

Note: In this example I have included the project code so you can see which project it refers to. I have also not used AS with the result of MIN.

Note: MIN can be used on dates

MAX

The MAX function returns the largest value from a series of records using the same principles as demonstrated with MIN above.

AVG

The AVG function returns the average value from a series of records. So, to return the average salary of all employee have you would write:

SELECT AVG(Months_Allocated_To_Project) AS average_no_of_monthsFROM emp_on_project;

You may want to refine this further so that the result is for example, to two decimal places:

SELECT FORMAT(AVG(Months_Allocated_To_Project),2) AS average_no_of_months

FROM emp_on_project;

Note: Format (number, decimal places)

ORDER BY

The ORDER BY clause sorts the results of a query by the specified field. If we wanted to display data from the emp_on_project table ordered by the dates employees joined the project you would write:

SELECT *
FROM emp_on_project
ORDER BY Date_Joined_Project;

Now in descending order:

SELECT *
FROM emp_on_project
ORDER BY Date_Joined_Project DESC;

We can also sort by more than one field. This example sorts by date joined project and then by the months allocated to project – in this case both as ASC as nothing is specified but one (or both) could be DESC.

SELECT * FROM emp_on_projectORDER BY Date_Joined_Project, Months_Allocated_To_Project;

GROUP BY

The GROUP BY clause is used when you have SELECTed an aggregate function as a field (e.g. COUNT). GROUP BY groups the records so that one value is returned for each group then runs the aggregate function described in the SELECT statement. Here we are counting the number of times an employee number is used in emp_on_project so we can see the number of projects an employee is/has worked on.

SELECT Emp_No, COUNT(Emp_No) AS No_of_Projs
FROM emp_on_project
GROUP BY Emp_No;

What if we want to include the total number of months an employee has been allocated to all their respective projects?

SELECT Emp_No, COUNT(Emp_No)AS No_of_Projs,
Sum(Months_Allocated_To_Project) AS total_months_allocated
FROM emp_on_project
GROUP BY Emp_No;

HAVING

The HAVING enables you to use aggregate functions in a comparison statement, providing for aggregate functions that WHERE provides for SELECT statements producing individual rows.

SELECT Proj_Code, COUNT(Proj_Code) AS No_of_staff,
SUM(Months_Allocated_To_Project) AS total_months

FROM emp_on_project
GROUP BY Proj_Code
HAVING total_months>9;

Note: Your HAVING clause must refer to a field that has been SELECTED – not just a field that is in the table concerned.

SELECT Proj_Code, COUNT(Proj_Code) AS No_of_staff
FROM emp_on_project
GROUP BY Proj_Code
HAVING No_of_staff>1;

Note: Usually the HAVING clause follows a GROUP BY clause.

Combining Clauses

If we wanted to find out how many projects have more than one staff allocated to them or that have staff allocated to a project for more than 9 months you would write:

SELECT Proj_Code, COUNT(Proj_Code) AS No_of_staff,
SUM(Months_Allocated_To_Project) AS total_months
FROM emp_on_project
GROUP BY Proj_Code
HAVING No_of_staff>1
OR total_months>9;

Construct SQL for the following queries

Display a list of all Projects with the titles in uppercase
Display a list of the number of months allocated to projects (along with project codes and employee numbers) showing what would happen if each project was extended by 33% - format to 0 decimal places so that it returns a whole number)
Total number of projects
First date an employee was allocated to a project
Maximum number of months an employee has been allocated to a project
Average grade an employee is on– format to 1 decimal place
Display all employees in descending order of grade
Display the total number of employees on each pay grade