A small example of grouping and summation in oracle

A small example of grouping and summation in oracle

Cold weather in April 9, 2015

Table gw_log is designed as follows:

Name Type Nullable Default Comments ------------ ----------- -------- ------- ---------- ID VARCHAR2 (50) Message id SERVICE_ID VARCHAR2 (20) Y service ID REQ_TIME date y request time INVOKE_TIME date y call time status char (1) Y '0' 0: failed, 1: Successful RESP_TIME date y response time USER_NAME VARCHAR2 (20) Y username SERVICE_TIME date y call service end time DESCN VARCHAR2 (256) Y description

Count the total number of records that pass the verification and fail each day: successful SQL statements:

-- Method 1 select req_time, sum (decode (status, '0', 1, 0) fail, sum (decode (status, '1', 1, 0 )) success from gw_log group by req_time;

The execution result is as follows:

Of course, the decode () function can also be used as the case function.

-- Method 2 select distinct. req_time,. fail, B. success from (select req_time, count (*) fail from gw_log where status = '0' group by req_time) a right join (select req_time, count (*) success from gw_log where status = '1' group by req_time) B on. req_time = B. req_time [Reference: select * from (select * from emp) e cross join (select * from book) B]

The execution result is as follows:

0 is not returned if count has no records, because there is a group by clause.
If it is not grouped (that is, there is no Group By), it will certainly return a 0.
An external connection is required to return 0 for count with groups.

Failed SQL statement:

-- Method 3 select * from (select. req_time, count (*) success from gw_log a where. status = '1' group by req_time union select B. req_time, count (*) fail from gw_log B where B. status = '0' group by B. req_time) g

The execution result is as follows: