SQL - Representing SUM's after using CASE to transform STR -> INT

Question

I am sorry for what may be a long post in advance.

Background:
I am using Rational Team Concert (RTC) which stores work item data in conjunction with Jazz Reporting Service to create reports. Using the Report Builder tool, it allows you to write your own queries to pull data as a table, and has its own interface to represent the table as a graph.

There is not much options for of graphing; the chart type defaults as a count, unless you specify it to show a sum. In order to graph by sum, the data must be a number rather than a string. By default, the Report Builder assumes all variables in the SELECT statement are strings.

The data which I will be using are a bunch of work items. Each work item is associated to a team (A, B) and has a work estimation number (count1, count2).

Item # | Team | Work   |
------------------------
123    |  A   | count1 |
------------------------
124    |  A   | count2 |
------------------------
125    |  B   | count2 |
------------------------
....

Problem:
Since the work estimation is entered as a Tag, the first step was to use a CATCH WHEN block when using SELECT to transform count1 -> 1, and count2 -> 2 (the string tag to an actual number which can be summed). This resulted in a table with numbers 1 and 2 in place of the typed tag (good so far).

Item # | Team | Work   |
------------------------
123    |  A   |   1    |
------------------------
124    |  A   |   2    |
------------------------
125    |  B   |   2    |
------------------------
....

The problem is that I am trying to graph by sum, which means getting the tool to identify the variables in the SELECT statement as numbers, except for some reason any variable I declare in a SELECT statement is always viewed as a string (The tool has a table of the current columns i.e. variables in the SELECT, along with that the tool identifies as its variable type).

Attempted Solutions:
The first query I did was to return a table of each work item with its team name and work estimate

SELECT T1.NAME,
       (CASE WHEN T1.TAGs='count1' THEN 1 ELSE 2 END) AS WORK
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73

Which resulted in

 Team | Work   |
----------------
 A    |   1    |
----------------
 A    |   2    |
----------------
 B    |   2    |
----------------
....

but the tool still sees the numbers as strings. I then tried explicitly casting the CASE to an integer, but resulted in the same issue

...
CAST(CASE WHEN T1.TAGs='count1' THEN 1 ELSE 2 END AS Integer) AS WORK
...

Which again the tool still represents as a string.

Current Goal:
As I cannot confirm if the tool has an underlying problem, compatibility issues with queries, etc. What I believe will work now would be to return a table with 2 rows: The sum of the work for each team

       |Sum of 1's and 2's  |
-----------------------------
Team A |  SUM(1) + SUM(2)   |
-----------------------------
Team B |  SUM(1) + SUM(2)   |
-----------------------------

What I am having trouble with is using sub queries to use SUM to sum the data. When I try SUM(CASE WHEN ... END) AS TIME2 I get an error that "Column modifiers AVG and SUM apply only to number attributes". This has me thinking that I need to have a sub query which returns the column after the CASE, and then SUM that, but I am sailing into uncharted waters and can't seem to get the syntax to work.

I understand that a post like this would be better off on the product help forum. I have tried asking around but cannot get any help. The solution I am proposing of returning the 2 row/column table should bypass any issues the software may have, but I need help sub-querying the SUM when using a case.

I appreciate your time and help!

EDIT 1:
Below is the full query code which preforms the CASE correctly, but still causes with the interpreted type by the tool:

SELECT  
   T1.Name,
   CAST(CASE WHEN T1.TAGS='|release_points_1|' THEN 1 ELSE (CASE WHEN T1.TAGS='|release_points_2|' THEN 2 ELSE 0 END) END AS Integer) AS TAG,
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73 
AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL

Answer 1

This small adjustment to your current query should work:

SELECT  
   T1.Name,
   SUM(CAST(CASE WHEN T1.TAGS='|release_points_1|' THEN 1 ELSE (CASE WHEN T1.TAGS='|release_points_2|' THEN 2 ELSE 0 END) END AS Integer)) AS TAG,
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73 
AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL
GROUP BY T1.Name