Sum year, month and day - Oracle SQL

We can use some algorithm to calculate the number of days, based on conditions

• one year = 365 days
• one month = 31 days

1. Year : (year * 365 + month * 31 + day) and division by 365 to get the total year of number.
2. Month : (month * 31 + day) and division by 31 to get the total month of number, but we need to get mod from that because total month numbers will be carried if that higher than 12.

as this query.

select CAST(SUM((year * 365 + month * 31 + day) / 365) AS INT) years  ,
       CAST(MOD(SUM(month * 31 + day)/31, 12) AS INT) month,
       MOD(SUM(day) , 31) day
from 
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 10 as month, 25 as day from dual 
) t1;

sqlfiddle

If 1 year = 365 days and 1 month = 31 days then:

SELECT TRUNC(SUM(year*365 + month*31 + day)/365) AS years,
       TRUNC(MOD(SUM(month*31 + day), 365)/31) AS month,
       MOD(MOD(SUM(month*31 + day), 365), 31) AS day
FROM
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 13 as month, 25 as day from dual 
) t1;

Which outputs:

YEARS	MONTH	DAY
3	7	11

If 1 year = 12 months and 1 month = 31 days then you want:

SELECT TRUNC(SUM(year + month/12 + day/31/12)) AS years,
       TRUNC(MOD(SUM(month + day/31), 12)) month,
       MOD(SUM(day) , 31) day
FROM
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 13 as month, 25 as day from dual 
) t1;

Which outputs:

YEARS	MONTH	DAY
3	7	4

db<>fiddle here