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

Source: Internet
Author: User

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


 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.