I have 2 different tables, and 'category type' is stored differently in each table. As an example, it might be 'Done' in table 1 but 'Implemented' in table 2. I'm trying to aggregate results of the 2 from a single SQL query such that I know states of each category across 4 states:

1. Done
2. In progress
3. Additional information requested
4. Backlog

I'm running the below query however the output changes the name of the issue_status, however, it does not return aggregate results. Just edits the name of the outputs to match the way I'm telling it to. Any suggestions?

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, count(*)
FROM raw.JIRA_ISSUES
             WHERE PROJECT_KEY in ('Project1', 'Project2')
and issue_status != ('Canceled')
and created_date > ('2021-09-03')
GROUP BY (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), issue_status
ORDER BY 2;

The table (project 1) is structured as follows

The table (project 2) is structured as follows

CodePudding user response：

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.