A commonly used SQL statement for report statistics

In common system development, we often encounter similar report statistics function requirements.

In common system development, we often encounter similar report statistics function requirements.

Generally, a value is counted by date range. Each column is the classification of statistics. For example:

The following example shows how to write an SQL statement: (Note: statistics are calculated based on the "date" field)
The Code is as follows:
Select date, optional field 1, optional field 2, sum (type 1) Type 1, sum (Type 2) Type 2, sum (Type 3) Type 3, sum (Type 4) type 4, sum (type 5) type 5, sum (Type 6) type 6 from (
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field 2, count (primary key field) type 1, 0 type 2, 0 Type 3, 0 type 4, 0 type 5, 0 type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2
Union all
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field 2, 0 Type 1, count (primary key field) Type 2, 0 Type 3, 0 type 4, 0 type 5, 0 type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2
Union all
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field type 2, 0 Type 1, 0 type 2, count (primary key field) Type 3, 0 type 4, 0 type 5, 0 type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2
Union all
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field type 2, 0 Type 1, 0 type 2, 0 Type 3, count (primary key field) type 4, 0 type 5, 0 type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2
Union all
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field type 2, 0 Type 1, 0 type 2, 0 Type 3, 0 type 4, count (primary key field) type 5, 0 type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2
Union all
Select to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd') date, optional field 1, optional field type 2, 0 Type 1, 0 type 2, 0 Type 3, 0 type 4, 0 type 5, count (primary key field) type 6 from Table Name
Where 1 = 1
Group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), select Field 1, select Field 2) where 1 = 1 group by to_date (to_char (date, 'yyyy-MM-dd'), 'yyyy-mm-dd'), optional field 1, optional field 2

The optional field 1 and optional field 2 can be added at will. It should be noted that the added optional field must be included after group.