@[TOC] Query using the database file download

An aggregate function is a function that returns a single value as input from a collection of values. SQL provides five built-in aggregation functions:

  • Average value: AVG
  • Minimum value: min
  • Maximum value: Max
  • Total: the sum
  • Count: the count

The input for sum and AVg must be a set of numbers, but other operators can also operate on sets of non-numeric data types.

1. Basic aggregation

  • Find the average salary for ‘Computer Science ‘department

    select avg(salary) as avg_salary
from instructor
where dept_name='Comp.Sci.';
Copy the code

  • When calculating the average value, pay attention to the retention of duplicate primitives, but some cases need to delete duplicate primitives for aggregation, such as finding the number of teachers teaching courses in spring 2010

    select count(distinct ID)
from teaches
where semester='Spring' and year=2010;
Copy the code

  • Count the number of primitives in the relationship: count(*)

2. Gather in groups

  • The group by clause

    • One or more attributes given by the group BY clause are used to construct a group. All primitives with the same value on all attributes in the group BY clause will be grouped into the same group.

      • Example: Find the average salary for each department:
    select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
Copy the code

    • whenSQLWhen querying groups, make sure that the only properties that appear in the SELECT statement but are not aggregated are those that appear in the group by clause. Otherwise such a query is wrong);

  • Having clause

    • Having: For group qualifiers

    • Example: Find the average salary of faculty in departments where the average departmental salary exceeds $42,000

      select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000
Copy the code

      • Any properties that appear in the HAVING statement but are not aggregated can only be those that appear in the group by clause.

  • The meaning of a query that contains an aggregate, group by, or having clause can be understood by the following sequence of operations:

    • First, the from clause computes the relationship;
    • If a WHERE clause is present, the WHERE clause filters the relational meta-ancestor;
    • If the group by clause is present, the filtered progenitors are grouped;
    • If the having clause is present, filter the group;
    • Finally, execute select;

3. Aggregation of null and Boolean values:

  • Assume that some ancestors in the instructor relationship take a null value on salary, aiming at the following query:

    • select sum(salary) from instructor

      The SQL standard does not consider the sum to be null, but ignores null values in the sum’s value set

  • Aggregate functions handle null values according to the following principles: all aggregate functions except count(*) ignore null values in the input set;

    • As the null value is ignored, the input set participating in the aggregation function operation may be empty, so the value of the empty set count operation is set to 0, and other aggregation operations return null value when the input is empty set.

  • Boolean:

    • Use some and every aggregation functions;

References: [1] Abraham Silberschatz, Henry F Korth, S Sudarshan． Database System Concepts. New York: McGraw-Hill, 2010 Database System Concepts