Oracle counts the sum of 1-12 months of data in a given year (very useful)

Practical cases

For the January 2015 to 12 months, the total number of people and the actual number of people from January 2014 to 12 months of the previous year have been found, and no month has shown a population of 0. Similar effects

Create a table

CREATE TABLE personsituation (  ID number not    null,  rdate DATE,  nums number  )

Inserting data

INSERT into personsituation (ID, rdate, nums) VALUES (1, to_date (' 26-01-2015 ', ' dd-mm-yyyy '), and 131); INSERT INTO Personsitua tion (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 PER Sonsituation (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_d Ate (' 25-01-2013 ', ' dd-mm-yyyy '), 211), insert into personsituation (ID, rdate, nums) VALUES (to_date (' 25-01-2013 ', ' DD -mm-yyyy '), 251); Insert inTo Personsituation (ID, rdate, nums) VALUES (one, to_date (' 25-01-2013 ', ' dd-mm-yyyy '), 262); INSERT into personsituation (ID , Rdate, Nums) VALUES (to_date (' 25-08-2015 ', ' dd-mm-yyyy '), 233), insert into personsituation (ID, rdate, nums) values (To_date (' 25-01-2013 ', ' dd-mm-yyyy '), 211); INSERT into personsituation (ID, rdate, nums) VALUES (to_date (' 25-02-2 014 ', ' dd-mm-yyyy '), 222); INSERT into personsituation (ID, rdate, nums) VALUES (to_date (' 25-03-2012 ', ' dd-mm-yyyy '), 2 INSERT into personsituation (ID, rdate, nums) VALUES (to_date (' 25-01-2012 ', ' dd-mm-yyyy '), 219);

The common statistics in the following SQL statement, only query to the data for some months, if there is nothing to show, and obviously does not meet the 1-12 months of 12 data statistics

Select To_char (rdate, ' yyyy-mm ') rdate,sum (nums) nums from Personsituation where To_char (rdate, ' yyyy ') = ' + ' GROUP BY _char (rdate, ' yyyy-mm ') Order by rdate

The correct analysis is: Must be 12 data, and is the result of the statistics of 12 data, starting from here to write the following SQL statement, display 12 columns of statistics, first query for one year of data, and then connect another data splicing

Select SUM (Decode (To_char (rdate, ' mm '), ' nums,0 ') nums01,sum (Decode (To_char (rdate, ' mm '), ' "", "nums,0") NUMS02,       sum (decode (rdate, ' mm '), ' To_char ', nums,0)) Nums03,sum (Decode (To_char (rdate, ' mm '), ' "," nums,0 ") Nums04,        sum (Decode (To_char (rdate, ' mm '), ' nums,0 ') nums05,sum (Decode (To_char (rdate, ' mm '), ' ", nums,0)) Nums06,       sum (Decode (To_char (rdate, ' mm '), ' ", nums,0)" Nums07,sum (Decode (To_char (rdate, ' mm '), ' ", nums,0") nums08,       sum (decode (rdate, ' mm '), ' To_char ', nums,0)) Nums09,sum (Decode (To_char (rdate, ' mm '), ' ten ', nums,0)) Nums10,       sum (Decode (To_char (rdate, ' mm '), ' one ', nums,0)) Nums11,sum (Decode (To_char (rdate, ' mm '), ' one ', nums,0)) nums12 From           personsituation            

And the front-end page display results have two possibilities, one is horizontal display, one is portrait display, you can use the following SQL statement to transform the column to get the following results

SELECT * FROM (  select sum (Decode (To_char (rdate, ' mm '), ' nums,0 ') nums01,sum (Decode (To_char (rdate, ' mm '), ' 02 ', nums,0)) NUMS02,     sum (Decode (To_char (rdate, ' mm '), ' nums,0 ', nums03,sum)) (Decode (To_char (rdate, ' mm '), ' 04 ', nums,0)) Nums04,      sum (Decode (To_char (rdate, ' mm '), ' on ', nums,0 ') nums05,sum (Decode (To_char (rdate, ' mm '), ' 06 ', nums,0)) Nums06,     sum (Decode (To_char (rdate, ' mm '), ' "," nums,0 ") Nums07,sum (Decode (To_char (rdate, ' mm '), ' 08 ', nums,0)) nums08,     sum (Decode (To_char (rdate, ' mm '), ' the ' nums,0 ') nums09,sum (Decode (To_char (rdate, ' mm '), ' 10 ', nums,0)) Nums10,     sum (Decode (To_char (rdate, ' mm '), ' one ', nums,0)) Nums11,sum (Decode (To_char (rdate, ' mm '), ' 12 ', nums,0)) nums12 from         personsituation          where To_char (rdate, ' yyyy ') = ' + ')  Unpivot (sum2015 for years in ( NUMS01,NUMS02,NUMS03,NUMS04,NUMS05,NUMS06,NUMS07,NUMS08,NUMS09,NUMS10,NUMS11,NUMS12))

The last step is to compare data with previous years using the left JOIN query

Select a.years,a.sum2015,b.sum2014 from (SELECT * FROM (select SUM (Decode (To_char (rdate, ' mm '), ' ', nums,0)) nums 01,sum (Decode (To_char (rdate, ' mm '), ' "nums,0") NUMS02, sum (Decode (To_char (rdate, ' mm '), ' "nums,0") nums03,sum ( Decode (To_char (rdate, ' mm '), ' "nums,0") Nums04, sum (Decode (To_char (rdate, ' mm '), ' ", nums,0") Nums05,sum (Decode ( To_char (rdate, ' mm '), ' ", nums,0)" Nums06, sum (Decode (To_char (rdate, ' mm '), ' ", nums,0") Nums07,sum (Decode (To_char ( Rdate, ' mm '), ' nums,0 ', ' nums08 ', sum (Decode (To_char (rdate, ' mm '), ' ['], ' nums,0 ') nums09,sum (Decode (To_char (Rdate, ' MM ') Nums10, sum (Decode (To_char (rdate, ' mm '), ' one ', nums,0) nums11,sum (Decode (To_char (rdate, ' mm ')), ' nums,0 ' (nums,0)) nums12 from Personsituation where To_char (rdate, ' yyyy ') = ' + ') 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'), ' nums,0 ', Nums01,sum (Decode (To_char (rdate, ' mm '), ' "nums,0") NUMS02, sum (Decode (To_char (rdate, ' mm '), ' 03 ', nums,0)) Nums03,sum (Decode (To_char (rdate, ' mm '), ' "," nums,0 ") Nums04, sum (Decode (To_char (rdate, ' mm '), ' on ', nums,0)) Nums05,sum (Decode (To_char (rdate, ' mm '), ' ", nums,0)" Nums06, sum (Decode (To_char (rdate, ' mm '), ' ", nums,0") Nums07, SUM (Decode (To_char (rdate, ' mm '), ' m ', nums,0)) nums08, sum (Decode (To_char (rdate, ' mm '), ' a ', ' nums,0 ') nums09,sum ( Decode (To_char (rdate, ' mm '), ' ten ', nums,0)) Nums10, sum (Decode (To_char (rdate, ' mm '), ' one ', nums,0)) Nums11,sum (Decode ( To_char (rdate, ' mm '), ' a ', ' 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

Implementation with the same data, you can change the NUMS01...NUMS12 in SQL to January ... December

Row to column, column change to have not know the place can refer to the article: http://blog.csdn.net/xiaokui_wingfly/article/details/42419207 feel good Remember to click like browse, thank you

Oracle counts the sum of 1-12 months of data in a given year (very useful)