Statistics for each month of the year

Use scenarios : The monthly expenditure of someone in a certain year in the expenditure table

1. Create the following table （oracle）

     -- The expenditure table  
    create table PAY  
    (  
      ID       NUMBER(11) not null,  
      PAY_NAME VARCHAR2(100),  
      AUTHOR   VARCHAR2(100),  
      TYPE_ID  NUMBER(11),  
      PRICE    FLOAT, -- amount of money  
      BRIEF    VARCHAR2(1000),  
      USERID   NUMBER(11), -- user ID  
      TIMES    DATE, -- Time  
      PRIMARY KEY(ID)  
    )

2. Insert test data

    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (1, '1', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (0, '3', '3', 1, 3, '3', 1, to_date('12-10-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (2, '2', '2', 1, 2, '2343444', 1, to_date('12-10-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (6, '6', '1', 0, 4000, '1', 1, to_date('12-08-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (5, '5', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (4, '4', '1', 0, 100, '1', 1, to_date('12-09-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (7, '4', '1', 0, 566, '1', 1, to_date('03-03-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (11, '4', '1', 0, 566, '1', 1, to_date('03-04-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (10, '4', '1', 0, 566, '1', 1, to_date('03-05-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (9, '4', '1', 0, 566, '1', 1, to_date('03-06-2012', 'dd-mm-yyyy'));  
    insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)  
    values (8, '4', '1', 0, 566, '1', 1, to_date('03-07-2012', 'dd-mm-yyyy'));  
    commit; 

3. Statistics sql( Inquire about userid by 1 Users of 2012 Monthly expenditure in )

    SELECT SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 1, B.PRICE, 0)) AS A,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 2, B.PRICE, 0)) AS B,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 3, B.PRICE, 0)) AS C,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 4, B.PRICE, 0)) AS D,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 5, B.PRICE, 0)) AS E,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 6, B.PRICE, 0)) AS F,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 7, B.PRICE, 0)) AS G,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 8, B.PRICE, 0)) AS H,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 9, B.PRICE, 0)) AS I,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 10, B.PRICE, 0)) AS J,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 11, B.PRICE, 0)) AS K,  
           SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 12, B.PRICE, 0)) AS L  
      FROM PAY B  
     WHERE USERID = 1  
       AND EXTRACT(YEAR FROM B.TIMES) = '2012' 

4. The statistics are as follows

    A  B  C    D   E     F   G   H    I   J   K   L  
    0  0  566 566   566 566  566 4000 100  7   0   0 

5. Another way of writing ： For the operation of time, you can also use the following

    SELECT SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '01', B.PRICE, 0)) M1,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '02', B.PRICE, 0)) M2,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '03', B.PRICE, 0)) M3,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '04', B.PRICE, 0)) M4,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '05', B.PRICE, 0)) M5,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '06', B.PRICE, 0)) M6,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '07', B.PRICE, 0)) M7,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '08', B.PRICE, 0)) M8,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '09', B.PRICE, 0)) M9,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '10', B.PRICE, 0)) M10,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '11', B.PRICE, 0)) M11,  
           SUM(DECODE(TO_CHAR(B.TIMES, 'MM'), '12', B.PRICE, 0)) M12  
      FROM PAY B  
     WHERE USERID = 1  
       AND TO_CHAR(B.TIMES, 'yyyy') = '2012'

This article comes from the Internet , The copyright does not belong to me .