Table with 3 columns, too difficult for me

Question

I'm a very new SQL user and I have a large database with 3 columns: company names, dates, and the company stock return over the next month. How can I turn the monthly data into annual data in an efficient way? I have tried joining each month with the next 12 months so that there would be 12 columns that i could multiply together but that is too inefficient. All dates are the last day of the month. Any help would be greatly appreciated!

Here's what the table looks like:

Company Name              date          return
apple               11/30/2012            1.05
apple               12/31/2012             .97
apple                1/31/2013            1.01
apple                2/28/2013            1.04
ford                11/30/2012            1.05
ford                12/31/2012             .97
ford                 1/31/2013            1.01
BP                   6/30/2012             .95

I want the returns to be the returns for the next 12 months multiplied together. Can that be done efficiently?

You need to provide more information about your table structure and some sample data to get an answer — Elvin, Dec 11 '13 at 01:32
Also, what do you mean multiplied together for next 12 months... Or, do you just want a columnar report of each item and its returns shown for each individual month? — DRapp, Dec 11 '13 at 03:10
Barry and MWT0 solved it already, but I just have a list of 1 month returns for stocks. I want yearly returns. So i tried to make 12 columns that i could multiply together. It's not an effective method. — user3089171, Dec 11 '13 at 04:17

score 0 · Answer 1 · edited May 23 '17 at 11:57

If you are using ORACLE then you can use these methods:

SQL Fiddle

There is no PRODUCT aggregate function but you can write a custom aggregation function...

Oracle 11g R2 Schema Setup:

CREATE TYPE ProductAggregationType as OBJECT
(
  product NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ProductAggregationType) 
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ProductAggregationType, 
    value IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN ProductAggregationType, 
    RETURNValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ProductAggregationType, 
    ctx2 IN ProductAggregationType) RETURN NUMBER
);
/

CREATE OR REPLACE type body ProductAggregationType is 
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ProductAggregationType) 
RETURN NUMBER IS
BEGIN
  sctx := ProductAggregationType(1);
  RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ProductAggregationType, value IN NUMBER) RETURN NUMBER is
BEGIN
  self.product := self.product * value;
  RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN ProductAggregationType, 
    returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER is
BEGIN
  returnValue := self.product;
  RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ProductAggregationType, ctx2 IN ProductAggregationType) RETURN NUMBER is
BEGIN
  self.product := self.product * ctx2.product;
  RETURN ODCIConst.Success;
END;
END;
/

CREATE FUNCTION PRODUCT (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING ProductAggregationType;
/

CREATE TABLE data ( names, dates, "return" ) AS
          SELECT 'A', TO_DATE( '31-01-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '28-02-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-03-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-04-2021', 'DD-MM-YYYY' ), 1.04 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-05-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-06-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-07-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-08-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-09-2021', 'DD-MM-YYYY' ), 1.03 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-10-2021', 'DD-MM-YYYY' ), 0.90 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-11-2021', 'DD-MM-YYYY' ), 1.00 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-12-2021', 'DD-MM-YYYY' ), 1.12 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-01-2022', 'DD-MM-YYYY' ), 0.90 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '28-02-2022', 'DD-MM-YYYY' ), 1.10 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-03-2022', 'DD-MM-YYYY' ), 1.00 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-01-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '28-02-2021', 'DD-MM-YYYY' ), 0.98 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-03-2021', 'DD-MM-YYYY' ), 1.03 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-04-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-05-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-06-2021', 'DD-MM-YYYY' ), 0.96 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-07-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-08-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-09-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-10-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-11-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-12-2021', 'DD-MM-YYYY' ), 1.08 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-01-2022', 'DD-MM-YYYY' ), 0.95 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '28-02-2022', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-03-2022', 'DD-MM-YYYY' ), 1.00 FROM DUAL
/

Query 1:

SELECT names,
       EXTRACT( YEAR FROM dates ) AS year,
       PRODUCT( "return" ) AS total_return
FROM   data
GROUP BY names, EXTRACT( YEAR FROM Dates )
ORDER BY year

Results:

| NAMES | YEAR |   TOTAL_RETURN |
|-------|------|----------------|
|     A | 2021 | 1.165456536894 |
|     B | 2021 | 1.176962855452 |
|     A | 2022 |           0.99 |
|     B | 2022 |         0.9975 |

Query 2:

Or you can use the power of logarithms to calculate the product:

SELECT names,
       EXTRACT( YEAR FROM dates ) AS year,
       EXP(SUM(LN( "return" ))) AS total_return
FROM   data
GROUP BY names, EXTRACT( YEAR FROM Dates )
ORDER BY year

Results:

| NAMES | YEAR |   TOTAL_RETURN |
|-------|------|----------------|
|     A | 2021 | 1.165456536894 |
|     B | 2021 | 1.176962855452 |
|     A | 2022 |           0.99 |
|     B | 2022 |         0.9975 |

I used your query 1 with some adjustments for nulls and product function. Thanks so much for taking the time, it was super helpful! — user3089171, Dec 11 '13 at 02:16
Glad to assist - if it answered your problem then please accept the answer. — MT0, Dec 11 '13 at 02:18

score 0 · Accepted Answer · answered Dec 11 '13 at 02:04

0

Something like this should work

SELECT 
      CompanyName, 
      YEAR(date), 
      EXP(SUM(LOG(return))) As AnnualReturn
FROM  yourTable
GROUP BY CompanyName, YEAR(date)
ORDER BY CompanyName, YEAR(date)

The details of such things as the YEAR(..) function vary by the specific DBMS product, but this is the basic approach.

answered Dec 11 '13 at 02:04

RBarryYoung

55,398
14
96
137

Thanks Barry! a simple but effective template is exactly what i needed. – user3089171 Dec 11 '13 at 02:18

Table with 3 columns, too difficult for me

2 Answers2