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

Source: Internet
Author: User

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)

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.