Interpret different results and return sum of count

Solution 1:

GROUP BY with aliases gets a little dicey.

Instead, consider using a common table expression (CTE) by way of the WITH clause to tidy this up. E.g.,

WITH subselect as (
  SELECT (case when issue_status in ('Done','Change Implemented','Assessed and scoped') then 'Done'
         when issue_status in ('In progress','In review','Scoping') then 'In progress'
         when issue_status in ('Additional information requested','Additional information required','More Information Required')  then 'Additional information requested'
         when issue_status = 'Backlog' then 'Backlog'
         end) as issue_status
   FROM raw.JIRA_ISSUES
         WHERE PROJECT_KEY in ('Project1', 'Project2')
     and issue_status != ('Canceled')
     and created_date > ('2021-09-03')
)
select subselect.issue_date,
       count(*)
  from subselect
 group by subselect.issue_date
 order by 2;

One nice thing about using WITH is that you can test out your subquery independently, and then get a better sense of what it looks like before you do your counting and grouping. This will help you detect oversights in your query. Furthermore, once you solidify that subquery, you can write new queries around it (e.g., move the created_date criteria into the outer query) and always leave that subquery in a pristine working state.