Showing Monthly Totals From Multiple Columns In Postgresql

I am looking to see the number of firstexam patients seen per month for a given date range this year vs last year, and compare that to the total number of patients seen per month f

Solution 1:

SQL Fiddle

select
    to_char(('2012-' || m || '-01')::date, 'Month'),
    thisyear, lastyear, totalthisyear, totallastyear
from (
    select
        extract(month from m) as m,
        sum(case
            when firstexam between '2013-01-01' and '2013-12-31' then firstexam_count
            else 0 end
        ) as thisyear,
        sum(case
            when firstexam between '2012-01-01' and '2012-12-31' then firstexam_count
            else 0 end
        ) as lastyear,
        sum(case
            when lastexam between '2013-01-01' and '2013-12-31' then lastexam_count
            else 0 end
        ) as totalthisyear,
        sum(case
            when lastexam between '2012-01-01' and '2012-12-31' then lastexam_count
            else 0 end
        ) as totallastyear
    from
        generate_series (
            '2012-01-01'::date, '2013-12-31', '1 month'
        ) g(m)
        left join (
            select count(*) as firstexam_count, date_trunc('month', firstexam) as firstexam
            from patient_info
            where firstexam between '2012-01-01' and '2013-12-31'
            group by 2
        ) pif on firstexam = m
        left join (
            select count(*) as lastexam_count, date_trunc('month', lastexam) as lastexam
            from patient_info
            where lastexam between '2012-01-01' and '2013-12-31'
            group by 2
        ) pil on lastexam = m
    group by 1
) s
order by m