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.