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 NULLs.

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 NULLs 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:

LOCATIONSUM_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_COUNTDEPT
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.

Comments

Comments loading...