Oracle grouping statistics

Shortly after I got to work today, QQ Didi kept ringing. After reading the information, I was asked by a netizen to help me with the next SQL statement. The general meaning is to count the number of records in different situations in the heart_active field, group by time.

I thought about it and thought it was easy to do. So I immediately created a table with the following statement:

Create Table rfid_fixed_heart (input_date date,

Heart_active varchar2 (2 ));

Next, data is inserted into the rfid_fixed_heart table, the heart_active fields are 0 and 1, and the YYYY-MM-DD format data is inserted into input_date.

Then I wrote the following two SQL statements for her. Statement 1 and Statement 2 are a little different. Statement 1 quickly calculates the number of records with the heart_active field being different, statement 2 only counts the number of records when the heart_active field is 0 and 1, and the output formats of the two statements are different. The details are as follows:

Statement 1:

Select a. input_date, A. heart_active, sum (decode (A. heart_active, 1, 1, 0, 1 ))

From rfid_fixed_heart

Group by A. heart_active, A. input_date

Order by A. input_date DESC;

Statement 2:

Select a. input_date, sum (decode (A. heart_active, '0', '1') as heart_active_0,

Sum (decode (A. heart_active, '1', '1') as heart_active_1

From rfid_fixed_heart

Group by A. input_date;

Soon the results were fed back, failing to achieve the expected results, but from her results it could be seen that the input_date was inserted with the YYYY-MM-DD 24hh: MI: data in SS format cannot be grouped by date.

Since the data is inserted in YYYY-MM-DD 24hh: MI: SS format, to sort by date, you need to use the trunc function for input_date to intercept the date value.

Finally, the original two SQL statements are changed to the following statement:

Statement 3:

Select trunc (A. input_date, 'dd'), A. heart_active, sum (decode (A. heart_active, 1, 1, 0, 1 ))

From rfid_fixed_heart

Group by A. heart_active, trunc (A. input_date, 'dd ')

Order by trunc (A. input_date, 'dd') DESC;

Statement 4:

Select trunc (A. input_date, 'dd'), sum (decode (A. heart_active, '0', '1') as heart_active_0,

Sum (decode (A. heart_active, '1', '1') as heart_active_1

From rfid_fixed_heart

Group by trunc (A. input_date, 'dd ');

After giving the statement to the netizen, run the statement to meet the requirements. OK. To compile SQL statements, you must carefully consider the data particularity and table structure so that SQL statements can be applied to different environments.

See the SQL statement without using the decode function:

Select op_date, heart_active, sum (heart_active_0) as heart_active_0,

Sum (heart_active_1) as heart_active_1

From (select to_char (rfid_fixed_heart.input_date, 'yyyy-mm-dd') as op_date,

Heart_active as heart_active,

Case heart_active

When '0' then

Count (heart_active)

Else

0

End as heart_active_0,

Case heart_active

When '1' then

Count (heart_active)

Else

0

End as heart_active_1

From rfid_fixed_heart

Group by input_date, heart_active)

Group by op_date, heart_active

Order by op_date DESC

The result is as follows: