CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 772

Grouping several dates on results per month in Postgresql

I have the following table on a Postgres server that is consistently updated with new dates (with the oldest date being 3 years ago) but for sake of simplicity, let's just bring up 3 records:

id      date1         date2        date3 
1245    01/04/2018    05/04/2018   03/05/2018   
1255    01/04/2018    01/06/2018   04/06/2018 
1231    03/05/2018    07/05/2018   07/06/2018
1234    01/04/2017    01/04/2017   01/06/2018

I would like to create an sql view that will give me a total count of units per type of date

month     date1 date2 date3    
Jun       0     1     3   
May       1     1     1 
Apr       2     1     0
Apr17     1     1     0

Is there a simple way to do that? My sql so far looks like 

CREATE OR REPLACE VIEW monthly_view 
AS
SELECT row_number() OVER (ORDER BY b.id) AS id,
       'month',
       count(*) as date1,
       count(*) as date2,
       count(*) as date3
FROM mytable b
GROUP BY month
ORDER BY month desc

But obviously doesn't work

Upvotes: 1

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Start by generating the months. Then I think correlated subqueries are a simple way of doing the calculation:

select v.mon,
       (select count(*)
        from mytable t
        where to_char(date1, 'mon') = v.mon
       ) as date1,
       (select count(*)
        from mytable t
        where to_char(date2, 'mon') = v.mon
       ) as date2,
       (select count(*)
        from mytable t
        where to_char(date3, 'mon') = v.mon
       ) as date3
from (values ('jun', 6), ('may', 5), ('apr', 4)
     ) v(mon, mm) 
order by mm desc;

EDIT:

Although the above should be pretty reasonable, the following might have better performance:

select mon, sum( (which = 1)::int ) as date1, sum( (which = 2)::int ) as date2,
       sum( (which = 3)::int ) as date3
from t cross join lateral
     (values (1, to_char(date1, 'mon'), extract(month from date1)),
             (2, to_char(date2, 'mon'), extract(month from date2)),
             (3, to_char(date3, 'mon'), extract(month from date3))
     ) v(which, mon, mm)
group by mon, mm
order by mm desc;

Here is a rextester.

Upvotes: 1

Alexander Anufriev
Alexander Anufriev

Reputation: 189

Tested!

Luffydude, you want to do a bad thing, but that is solution ;)

    --all months
    with allmonths as (
        select to_char(date1, 'TMmon') as mm from tmp_a
        union
        select to_char(date2, 'TMmon') as mm from tmp_a
        union
        select to_char(date3, 'TMmon') as mm from tmp_a
    ),
    --count_in_date1
    count_in_date1 as (
        select
            t1.mm,
            count(t2.date1) as count_in_date1
        from
            allmonths t1 left join tmp_a t2 
                on to_char(t2.date1, 'TMmon') = t1.mm
        group by 
            t1.mm
    ),
    --count_in_date2
    count_in_date2 as (
        select
            t1.mm,
            count(t2.date2) as count_in_date2
        from
            allmonths t1 left join tmp_a t2 
                on to_char(t2.date2, 'TMmon') = t1.mm
        group by 
            t1.mm
    ),
    --count_in_date3
    count_in_date3 as (
        select
            t1.mm,
            count(t2.date3) as count_in_date3
        from
            allmonths t1 left join tmp_a t2 
                on to_char(t2.date3, 'TMmon') = t1.mm
        group by 
            t1.mm
    )
    --join all count together
    select t1.mm, count_in_date1, count_in_date2, count_in_date3 
    from count_in_date1 t1, count_in_date2 t2, count_in_date3 t3
        where t1.mm=t2.mm and t1.mm=t3.mm

Upvotes: 2

Related Questions