0

I have a table that contains revenue column and timestamp. I want to extract the data in the format shown below. Can someone please help point in the right direction? I have managed to extract and group by months by I also want the year columns. Any help will be appreciated.

2021 2022 Difference
Jan 100 300 200
Feb 530 403 -127
Mar 456 232 -224
Apr 120 122 2
May 223 302 79
Jun 564 322 -242
Jul 547 601 54
Aug 355 302 -53
Sub Total 4916 4606 -310 
SELECT monthName As Month, SUM(revenue) AS revenue, year
FROM (
  SELECT CASE
           WHEN t1.currency_id = 'CAD' THEN t1.amount
           ELSE round(t1.amount *  ifnull(t2.exchange_rate_c,1),2)
          END AS revenue,
         MONTH(t2.latest_status_date_c) AS Month,
         MONTHNAME(latest_status_date_c) AS monthName,
         YEAR(t2.latest_status_date_c) AS Year
  FROM suitecrm.opportunities t1
  LEFT JOIN suitecrm.opportunities_cstm t2 ON t1.id = t2.id_c
  WHERE t1.sales_stage IN (1,2,3) AND t1.deleted=0 AND t2.latest_status_date_c between '2022-01-01' and '2022-05-31'
) tblmain group by tblmain.monthName, year

I am having a hard time to format the display table

Maurice
  • 127
  • 1
  • 3
  • 13
  • Can you share a sample of your input tables "*opportunities*" and "*opportunities_cstm*" and update your expected output accordingly? – lemon Oct 13 '22 at 15:41

0 Answers0