Syntax¶

SELECT...FROM...[...]GROUPBYGROUPINGSETS(groupSet[,groupSet[,...]])[...]

Where:

groupSet::={<column_alias>|<position>|<expr>}

Copy

Parameters¶

column_alias

Column alias appearing in the query block’sSELECT list.

position

Position of an expression in theSELECT list.

expr

Any expression on tables in the current scope.

Usage notes¶

• Snowflake allows up to 128 grouping sets in the same query block.

• The output typically contains some NULL values. BecauseGROUPBYROLLUPmerges the results of two or more result sets, each of which wasgrouped by different criteria, some columns that have a single valuein one result set might have many corresponding values in theother result set. For example, if you do aUNION of a set ofemployees grouped by department with a set grouped by seniority, themembers of the set with the greatest seniority are not necessarily allin the same department, so the value of department_name is set toNULL. The following examples contain NULLs for this reason.

Examples¶

These examples use a table of information about nurses who are trained toassist in disasters. All of these nurses have a license as nurses (e.g.an RN has a license as a “Registered Nurse”), and an additional licensein a disaster-related specialty, such as search and rescue, radiocommunications, etc. This example simplifies and uses just two categoriesof licenses:

• Nursing: RN (Registered Nurse) and LVN (Licensed Vocational Nurse).

• Amateur (“ham”) Radio: Ham radio licenses include “Technician”, “General”, and “Amateur Extra”.

Here are the commands to create and load the table:

CREATEorreplaceTABLEnurses(IDINTEGER,full_nameVARCHAR,medical_licenseVARCHAR,-- LVN, RN, etc.radio_licenseVARCHAR-- Technician, General, Amateur Extra);INSERTINTOnurses(ID,full_name,medical_license,radio_license)VALUES(201,'Thomas Leonard Vicente','LVN','Technician'),(202,'Tamara Lolita VanZant','LVN','Technician'),(341,'Georgeann Linda Vente','LVN','General'),(471,'Andrea Renee Nouveau','RN','Amateur Extra');

Copy

This query usesGROUPBYGROUPINGSETS:

SELECTCOUNT(*),medical_license,radio_licenseFROMnursesGROUPBYGROUPINGSETS(medical_license,radio_license);

Copy

Output:

The first two rows show the count of RNs and LVNs (two types of nursinglicenses). The NULL values in the RADIO_LICENSE column forthose two rows are deliberate; the query grouped all of the LVNs together(and all the RNs together) regardless of their radio license, so theresults can’t show one value in the RADIO_LICENSE column for eachrow that necessarily applies to all the LVNs or RNs grouped in that row.

The next three rows show the number of nurses with each type of ham radiolicense (“Technician”, “General”, and “Amateur Extra”). The NULL valuefor MEDICAL_LICENSE in each of those three rows is deliberate becauseno single medical license necessarily applies to all members of eachof those rows.

+----------+-----------------+---------------+| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE ||----------+-----------------+---------------||        3 | LVN             | NULL          ||        1 | RN              | NULL          ||        2 | NULL            | Technician    ||        1 | NULL            | General       ||        1 | NULL            | Amateur Extra |+----------+-----------------+---------------+

The next example shows what happens when some columns contain NULL values.Start by adding three new nurses who don’t yet have ham radio licenses.

INSERTINTOnurses(ID,full_name,medical_license,radio_license)VALUES(101,'Lily Vine','LVN',NULL),(102,'Larry Vancouver','LVN',NULL),(172,'Rhonda Nova','RN',NULL);

Copy

Then run the same query as before:

SELECTCOUNT(*),medical_license,radio_licenseFROMnursesGROUPBYGROUPINGSETS(medical_license,radio_license);

Copy

Output:

The first 5 lines are the same as in the previous query.

The last line might be confusing at first – why is there a line that hasNULL in both columns? And if all the values are NULL, why is the COUNT(*)equal to 3?

The answer is that the NULL in the RADIO_LICENSE column of that rowoccurs because three nurses don’t have any radio license.(“SELECT DISTINCT RADIO_LICENSE FROM nurses” now returns four distinctvalues: “Technician”, “General”, “Amateur Extra”, and “NULL”.)

The NULL in the MEDICAL_LICENSES column occurs for the same reason thatNULL values occur in the earlier query results: the nurses counted in thisrow have different MEDICAL_LICENSES, so no one value (“RN” or “LVN”)necessarily applies to all of the nurses counted in this row.

+----------+-----------------+---------------+| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE ||----------+-----------------+---------------||        5 | LVN             | NULL          ||        2 | RN              | NULL          ||        2 | NULL            | Technician    ||        1 | NULL            | General       ||        1 | NULL            | Amateur Extra ||        3 | NULL            | NULL          |+----------+-----------------+---------------+

If you’d like, you can compare this output to the output of aGROUPBY without theGROUPINGSETS clause:

SELECTCOUNT(*),medical_license,radio_licenseFROMnursesGROUPBYmedical_license,radio_license;

Copy

Output:

+----------+-----------------+---------------+| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE ||----------+-----------------+---------------||        2 | LVN             | Technician    ||        1 | LVN             | General       ||        1 | RN              | Amateur Extra ||        2 | LVN             | NULL          ||        1 | RN              | NULL          |+----------+-----------------+---------------+