Help Center/ SecMaster/ User Guide/ Log Audit/ Query and Analysis Syntax - V2/ SecMaster SQL Syntax Reference/ Data Query Language (DQL) Syntax/ UNION
Updated on 2025-09-05 GMT+08:00
View PDF
Share

UNION

Column-Based GROUP BY

  • Function

    This clause is used to group a table based on columns.

  • Syntax Format 
    1
2
3
4
    		 
    SELECT [ ALL | DISTINCT ]   { * | projectItem [, projectItem ]* }
  FROM tableExpression
  [ WHERE booleanExpression ]
  [ GROUP BY { groupItem [, groupItem ]* } ]
  • Syntax Description

    Column-based GROUP BY can be categorized into single-column GROUP BY and multi-column GROUP BY.

    • Single-column GROUP BY indicates that the GROUP BY clause contains only one column.
    • Multi-column GROUP BY indicates that the GROUP BY clause contains multiple columns. The table will be grouped according to all fields in the GROUP BY clause. The records whose fields are the same are grouped into one group.
  • Precautions

    GroupBy generates update results in the stream processing table.

  • Example

    Group the student table according to the score and name fields and return the grouping results.

    1
2
    		 
    SELECT name,score, max(score) FROM student 
  GROUP BY name,score;

Expression-Based GROUP BY

  • Function

    This clause is used to group streams according to expressions.

  • Syntax Format 
    1
2
3
4
    		 
    SELECT [ ALL | DISTINCT ]   { * | projectItem [, projectItem ]* }
  FROM tableExpression
  [ WHERE booleanExpression ]
  [ GROUP BY { groupItem [, groupItem ]* } ]
  • Syntax Description

    groupItem can have one or more fields. The fields can be called by string functions, but cannot be called by aggregate functions.

  • Precautions

    None

  • Example

    Use the substring function to obtain the character string from the name field, group the student table according to the obtained character string, and return each sub character string and the number of records.

    1
2
    		 
    SELECT substring(name,6),count(name) FROM student
  GROUP BY substring(name,6);

GROUP BY Using HAVING

  • Function

    This clause filters a table after grouping it using the HAVING clause.

  • Syntax Format 
    1
2
3
4
5
    		 
    SELECT [ ALL | DISTINCT ]   { * | projectItem [, projectItem ]* }
  FROM tableExpression
  [ WHERE booleanExpression ]
  [ GROUP BY { groupItem [, groupItem ]* } ]
  [ HAVING booleanExpression ]
  • Syntax Description

    Generally, HAVING and GROUP BY are used together. You can use GROUP BY for grouping and the HAVING for filtering.

  • Precautions
    • If the filtering condition is subject to the results of GROUP BY, the HAVING clause, rather than the WHERE clause, must be used for search. HAVING and GROUP BY are used together. Use GROUP BY for grouping and HAVING for filtering.
    • Fields used in HAVING, except for those used for aggregate functions, must exist in GROUP BY.
    • The HAVING clause supports arithmetic operations and aggregate functions.
  • Example

    Group the transactions by num, use the HAVING clause to search for the records in which the maximum value derived from multiplying price with amount is higher than 5000, and return the filtered results.

    1
2
3
4
    		 
    SELECT num, max(price*amount) FROM transactions
  WHERE time > '2016-06-01'
  GROUP BY num
  HAVING max(price*amount)>5000;