Search code examples
sqldatesumrangemonthcalendar

Return Multiple Sums By Moth For Date Range

I've seen several versions of this problem out there but no solution to my specific one. I have two tables. One of which has 2 numbers to sum up. The tables have no restraints between them. I want a result set that returns the sums for each table by month for a given date range. For instance:

Month Total_Collections Total_Expenses

I've attached the schema for the tables:

enter image description here

enter image description here

Solution

  • As far as I understand Your Problem this solution should do what You expect , My Solution was tested at Oracle :

    It returns counts for given YEAR-MONTH key :

    SELECT exp.Month , exp.ExpenseCount , coll.CollectionCount 
FROM 
(SELECT  
    TO_CHAR(ExpenseDate, 'YYYY-MM') as Month,
    COUNT(1) as ExpenseCount 
    FROM Expenses 
    GROUP BY TO_CHAR(ExpenseDate, 'YYYY-MM')
) exp ,
(SELECT  
    TO_CHAR(CollectionDate, 'YYYY-MM') as Month,
    COUNT(1) as  CollectionCount
    FROM Collection  
    GROUP BY TO_CHAR(CollectionDate, 'YYYY-MM')
) coll 
WHERE exp.Month = coll.Month ;

    Example output :

    MONTH     | EXPENSECOUNT | COLLECTIONCOUNT 
2017-11           1              1 
2017-12           2              3