Oracle calculates the total data from 1 to 12 months in a year (very practical)

Oracle calculates the total data from 1 to 12 months in a year (very practical)
Case studies

Query the total number of real population from January 2015 to 12 months and the number of real population from January 2014 to 12 months in previous years. The number of months that are not found is 0. Similar results

Create a table

 

create table PERSONSITUATION(  id    NUMBER not null,  rdate DATE,  nums  NUMBER)

 

Insert data

 

 

insert into PERSONSITUATION (id, rdate, nums) values (1, to_date('26-01-2015', 'dd-mm-yyyy'), 131);insert into PERSONSITUATION (id, rdate, nums) values (2, to_date('27-01-2013', 'dd-mm-yyyy'), 232);insert into PERSONSITUATION (id, rdate, nums) values (3, to_date('18-10-2013', 'dd-mm-yyyy'), 222);insert into PERSONSITUATION (id, rdate, nums) values (4, to_date('20-01-2015', 'dd-mm-yyyy'), 232);insert into PERSONSITUATION (id, rdate, nums) values (5, to_date('28-01-2015', 'dd-mm-yyyy'), 422);insert into PERSONSITUATION (id, rdate, nums) values (6, to_date('26-02-2015', 'dd-mm-yyyy'), 232);insert into PERSONSITUATION (id, rdate, nums) values (7, to_date('29-01-2014', 'dd-mm-yyyy'), 225);insert into PERSONSITUATION (id, rdate, nums) values (8, to_date('31-01-2015', 'dd-mm-yyyy'), 111);insert into PERSONSITUATION (id, rdate, nums) values (9, to_date('25-01-2013', 'dd-mm-yyyy'), 211);insert into PERSONSITUATION (id, rdate, nums) values (10, to_date('25-01-2013', 'dd-mm-yyyy'), 251);insert into PERSONSITUATION (id, rdate, nums) values (11, to_date('25-01-2013', 'dd-mm-yyyy'), 262);insert into PERSONSITUATION (id, rdate, nums) values (12, to_date('25-08-2015', 'dd-mm-yyyy'), 233);insert into PERSONSITUATION (id, rdate, nums) values (13, to_date('25-01-2013', 'dd-mm-yyyy'), 211);insert into PERSONSITUATION (id, rdate, nums) values (14, to_date('25-02-2014', 'dd-mm-yyyy'), 222);insert into PERSONSITUATION (id, rdate, nums) values (15, to_date('25-03-2012', 'dd-mm-yyyy'), 209);insert into PERSONSITUATION (id, rdate, nums) values (16, to_date('25-01-2012', 'dd-mm-yyyy'), 219);

In common statistics, the following SQL statement only queries some months of data. If nothing is found, nothing is displayed, and 12 data statistics that apparently do not meet the requirements of 1-12 months

select to_char(rdate,'yyyy-mm') rdate,sum(nums) nums from personsituation where to_char(rdate,'yyyy')='2015' group by to_char(rdate,'yyyy-mm') order by rdate

The correct analysis is: it must be 12 data records and 12 data records of the statistical results. Write the following SQL statement to show the statistical data of 12 columns, first query the data for one year, and then connect to another data record.

 

 

select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04,        sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12           from personsituation            where to_char(rdate,'yyyy')='2015' 

The results displayed on the front-end page have two possibilities: horizontal display and vertical display. You can use the following SQL statement to convert columns to rows to get the following results:

 

 

select * from (  select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,     sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04,      sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,     sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,     sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,     sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12         from personsituation          where to_char(rdate,'yyyy')='2015' )  unpivot (sum2015 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )

 

The last step is to use left join to query data compared with previous years.

 

select A.years,A.SUM2015,B.SUM2014 from       (select * from (    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04,        sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12           from personsituation            where to_char(rdate,'yyyy')='2015'   ) unpivot (sum2015 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) Aleft join  (select * from (    select sum(decode(to_char(rdate,'mm'),'01',nums,0)) nums01,sum(decode(to_char(rdate,'mm'),'02',nums,0)) nums02,       sum(decode(to_char(rdate,'mm'),'03',nums,0)) nums03,sum(decode(to_char(rdate,'mm'),'04',nums,0)) nums04,        sum(decode(to_char(rdate,'mm'),'05',nums,0)) nums05,sum(decode(to_char(rdate,'mm'),'06',nums,0)) nums06,       sum(decode(to_char(rdate,'mm'),'07',nums,0)) nums07,sum(decode(to_char(rdate,'mm'),'08',nums,0)) nums08,       sum(decode(to_char(rdate,'mm'),'09',nums,0)) nums09,sum(decode(to_char(rdate,'mm'),'10',nums,0)) nums10,       sum(decode(to_char(rdate,'mm'),'11',nums,0)) nums11,sum(decode(to_char(rdate,'mm'),'12',nums,0)) nums12           from personsituation            where to_char(rdate,'yyyy')='2014'   ) unpivot (sum2014 for years in (nums01,nums02,nums03,nums04,nums05,nums06,nums07,nums08,nums09,nums10,nums11,nums12) )) B on A.years = B.years

To achieve the same data, you can change nums01. .. nums12 in SQL to January.

 

Select. years,. SUM1, B. SUM2 from (select * from (select sum (decode (to_char (rdate, 'mm'), '01', nums, 0) January, sum (decode (to_char (rdate, 'mm'), '02', nums, 0), December 31, sum (decode (to_char (rdate, 'mm'), '03', nums, 0, sum (decode (to_char (rdate, 'mm'), '04 ', nums, 0) April, sum (decode (to_char (rdate, 'mm '), '05 ', nums, 0) May, sum (decode (to_char (rdate, 'mm'), '06', nums, 0) June, sum (decode (to_char (rdate, 'mm'), '07 ', nums, 0), July, sum (decode (to_char (rdate, 'mm '), '08 ', nums, 0) August, sum (decode (to_char (rdate, 'mm'), '09', nums, 0) September, sum (decode (to_char (rdate, 'mm'), '10', nums, 0) October, sum (decode (to_char (rdate, 'mm '), '11', nums, 0) November, sum (decode (to_char (rdate, 'mm'), '12', nums, 0 )) december from personsituation where to_char (rdate, 'yyyy') = '000000') unexpect (sum1 for years in (January, February, March, April, May, June, July, August, september, October, November, December) Aleft join (select * from (select sum (decode (to_char (rdate, 'mm'), '01', nums, 0 )) january, sum (decode (to_char (rdate, 'mm'), '02', nums, 0), February, sum (decode (to_char (rdate, 'mm '), '03 ', nums, 0) March, sum (decode (to_char (rdate, 'mm'), '04', nums, 0) April, sum (decode (to_char (rdate, 'mm'), '05 ', nums, 0) May, sum (decode (to_char (rdate, 'mm '), '06 ', nums, 0) June, sum (decode (to_char (rdate, 'mm'), '07', nums, 0) July, sum (decode (to_char (rdate, 'mm'), '08 ', nums, 0), August, sum (decode (to_char (rdate, 'mm '), '09 ', nums, 0) September, sum (decode (to_char (rdate, 'mm'), '10', nums, 0) October, sum (decode (to_char (rdate, 'mm'), '11', nums, 0) November, sum (decode (to_char (rdate, 'mm '), '12', nums, 0) December from personsituation where to_char (rdate, 'yyyy') = '000000') unexpect (sum2 for years in (2014, January, February, March, B on. years = B. years