# Retrieval with a Set Function

This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Here's some examples of set functions, using the sample database we defined in our chapter on simple search conditions. To find the total number of employees with payroll records (retrieve the number of records in a Table):

```SELECT COUNT(*) AS pay_count
FROM   Payroll;
```

The result is:

`PAY_COUNT`
10

`COUNT(*)` counts all rows of a Table, regardless of `NULL`s.

To find the number of employees with known pay rates (retrieve the number of non-null values in a Column):

```SELECT COUNT(rate) AS pay_count
FROM   Payroll;
```

The result is:

`PAY_COUNT`
9

`COUNT`(column) eliminates `NULL`s before counting a Column's values.

To find the number of pay rates (retrieve the number of unique values in a Column):

```SELECT COUNT(DISTINCT rate) AS pay_count
FROM   Payroll;
```

The result is:

`PAY_COUNT`
5

The `DISTINCT` option eliminates duplicates before a set function is processed.

To find the sum of the pay rates by location (group a Table into like values combined with a set function):

```SELECT   location,
SUM(rate) AS sum_rate
FROM     Payroll
GROUP BY location;
```

The result is:

`LOCATION``SUM_RATE`
10TH FLOOR31.00
16TH FLOOR37.00
BASEMENT8.00
WAREHOUSE14.00

To find the number of employees in each department:

```SELECT   COUNT(empnum) AS emp_count,
dept
FROM     Employee
WHERE    dept<'D'
GROUP BY dept;
```

The result is:

`EMP_COUNT``DEPT`
2A
4B
1C

If you want to restrict your code to Core SQL, don't use the set functions `EVERY`, `ANY`, `SOME`, or `GROUPING`, don't use a set function unless it operates only on a <Column reference> that refers to a Column belonging to a Table named in the `FROM` clause, and when counting, always use `COUNT(*)`: don't use `COUNT(Column)` or `COUNT(ALL Column)` at all.