This topic describes how to use the GROUPING SETS clause to address the issue that you need to execute the UNION ALL clause multiple times to aggregate and analyze data from multiple dimensions. For example, you need to aggregate data in column a, column b, and both columns.

Syntax

SELECT [ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
FROM tableExpression
GROUP BY 
[GROUPING SETS { groupItem [, groupItem ]* } ];

Example

  • Test data
    username month day
    Lily 10 1
    Lucy 11 21
    Lily 11 21
  • Test statements
    SELECT  
        `month`,
        `day`,
        count(distinct `username`) as uv
    FROM tmall_item
    group by 
    grouping sets((`month`),(`month`,`day`));
  • Test results
    month day uv
    10 1 1
    10 null 1
    11 21 1
    11 null 1
    11 21 2
    11 null 2