[ad_1]
I am calculating the last 12-months count after joining multiple tables, my expected output is working is OK but it is not what I want?. I want to add another column with the name “Current Month”, so the basic idea is if I see a report for the month May, then it will start from Last year’s May till This year’s April and May as Current Month, total 13 columns counts. My intuition says window query will help me out on this, but I am now sure how I can do that.
select
c.name,
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'January' THEN 1 END) as "January",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'February' THEN 1 END) as "February",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'March' THEN 1 END) as "March",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'April' THEN 1 END) as "April",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'May' THEN 1 END) as "May",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'June' THEN 1 END) as "June",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'July' THEN 1 END) as "July",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'August' THEN 1 END) as "August",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'September' THEN 1 END) as "September",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'October' THEN 1 END) as "October",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'November' THEN 1 END) as "November",
SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'December' THEN 1 END) as "December"
from analytics_outbox mo
inner join analytics_outbox_recipient mor on mor.analytics_outbox_id = mo.id
inner join customer c on c.id = mo.customer_id
group by c.name
Current Output:
name |january|february|march |april |may |june|july|august|september|october|november|december|
----------------------------------+-------+--------+------+-------+-------+----+----+------+---------+-------+--------+--------+
ABC | | | 1| 2| | | | | | | | |
DEF | 11| 24| 34| 32| 19| | | | | | | |
GEH | 9| 3| 7| 18| 22| | | | | | | |
IJK | | | | 1| | | | | | | | |
[ad_2]