CODEWITHSUNDEEP
sqlaggregate-functionsfirebird
Mohamed Benattou
Mohamed Benattou

Reputation: 105

How to join tables and sum per month

I've 2 tables, T1 and T2 designed like that;

T1:

ID           DATE_1           NUM_VALUE_1           NUM_VALUE_2 
--           ------           -----------           -----------
1        01/01/2015              15000.00              10000.00
2        01/16/2015              25000.00               9000.00
3        02/06/2015               8500.00              12000.00
4        03/27/2015              21200.00              19000.00
5        07/21/2015              47800.00              21000.00

T2:

ID           DATE_2           NUM_VALUE_3 
--           ------           -----------
1        01/12/2015               8500.00
2        02/11/2015              11200.00
3        02/20/2015              35600.00
4        04/25/2015              20000.00

I want to show something like this;

Month        NUM_VALUE_1        NUM_VALUE_2         NUM_VALUE_3
-----        -----------        -----------         -----------
   01           40000.00           19000.00             8500.00
   02            8500.00           12000.00            46800.00
   03           21200.00           19000.00                0.00  
   04               0.00               0.00            20000.00
   05               0.00               0.00                0.00
   06               0.00               0.00                0.00
   07           47800.00           21000.00                0.00
   08               0.00               0.00                0.00
   09               0.00               0.00                0.00
   10               0.00               0.00                0.00
   11               0.00               0.00                0.00
   12               0.00               0.00                0.00

ie join the 2 tables by grouping them by the month of dates, and for every month I have to sum NUM_VALUE_1 and NUM_VALUE_2 (from T1) and NUM_VALUE_3 (from T2).

Upvotes: 1

Views: 218

Answers (2)

ain
ain

Reputation: 22759

Use the EXTRACT function to get the month out of date, something like:

select m, Sum(Num_Value_1), Sum(Num_Value_2), Sum(Num_Value_3)
from (
   select EXTRACT(MONTH from DATE_1) m, NUM_VALUE_1, NUM_VALUE_2, 0 as NUM_VALUE_3 from T1
   union all
   select EXTRACT(MONTH from DATE_2) m, 0 as NUM_VALUE_1, 0 as NUM_VALUE_2, NUM_VALUE_3 from T2
) GROUP BY m

Upvotes: 3

Tom H
Tom H

Reputation: 47462

I'm not sure of all of the syntax that Firebird supports, but hopefully this works:

SELECT
    Months.Month,
    SUM(T1.NUM_VALUE_1) AS Total_Num_Value_1,
    SUM(T1.NUM_VALUE_2) AS Total_Num_Value_2,
    SUM(T1.NUM_VALUE_3) AS Total_Num_Value_3
FROM
    (SELECT 1 AS Month UNION SELECT 2 AS Month UNION SELECT 3 AS Month UNION
     SELECT 4 AS Month UNION SELECT 5 AS Month UNION SELECT 6 AS Month UNION 
     SELECT 7 AS Month UNION SELECT 8 AS Month UNION SELECT 9 AS Month UNION
     SELECT 10 AS Month UNION SELECT 11 AS Month UNION SELECT 12 AS Month UNION) Months
LEFT OUTER JOIN T1 ON EXTRACT(MONTH FROM T1.DATE_1) = Months.Month
LEFT OUTER JOIN T2 ON EXTRACT(MONTH FROM T2.DATE_2) = Months.Month
GROUP BY
    Months.Month

Upvotes: 1

Related Questions