Return this years February records if any created, else last years February

Question

I am quite new to SQL. I want to count the number of records for February (or for all months) that are created in a table this year, if no records yet has not been created for the month i want to calculate last years count for the month. The result should then change over the year, when we get to 1:st of April the result should show 0 records.

I tried to use a Case, it "works" when there is no new records for the current year, it will show number of records created last year for the month, but for example january we have records created, and the sum becomes the sum of this years records in January plus records from January last year. I thought the Case When should exit when the first when is true. Any suggestions?

My code here:

  SELECT 
        COUNT(`user_activity`.`creation_date`) AS `ticket_count`
    FROM
        `user_activity`
    WHERE

    CASE
    
    WHEN (Year(creation_date)=Year(curdate()) and MonthName(curdate())='January' is not null) >0
        THEN ((MONTHNAME(`user_activity`.`creation_date`) = 'January') AND  Year(`user_activity`.`creation_date`)=year(curdate())))
    when(Year(creation_date)=Year(curdate())-1 and MonthName(curdate())='January' is not null) >0
        then
        ((MONTHNAME(`user_activity`.`creation_date`) = 'January') AND (Year(`user_activity`.`creation_date`)=year(curdate())-1))

    END;

Answer 1

One simple way to get your query is to run two queries that return one row for the respective year / month and apply a coalesce between them getting only the first counted value that HAS records. If nothing for this year, grab count from last year.

select
      case when coalesce( thisYear.Recs, 0 ) = 0
           then lastYear.Recs
           else thisYear.Recs ) end Ticket_Count
   from
      ( select count(*) Recs
           from User_Activity
           where creation_date >= '2022-02-01'
             AND creation_date < '2022-03-01' ) lastYear
      LEFT JOIN
      ( select count(*) Recs
           from User_Activity
           where creation_date >= '2023-02-01'
             AND creation_date < '2023-03-01' ) thisYear
         -- join always for principle, include joining "ON" clause
         -- since both queries above always return 1 row, 1=1 works
         on 1=1