Combine values from SQL results if value is found

Question

User-718146471 posted

Hello folks, here's what I am trying to do. I have results by category doing counts. What I want to do is three of the categories need to present a result as one instead of three separate results. My query results look like this:

Team                                                                          Projects
Team1               140
Team2               1
Team3               2
Team4               15
Team5               30
Team6               40
Team7               50
Team8               110
Team9               6
Team10             5

What I would like to do is combine the values from Team1, Team6, and Team7 into one result. I want this to be based on the team name as DeltaTeam for the name and value = 230. Thanks in advance!

Answer 1

User281315223 posted

If I understand you correctly, I believe that you could accomplish this using a CASE statement in conjunction with a GROUP BY clause:

SELECT     CASE
                WHEN Team IN ('Team1', 'Team6', 'Team7') THEN 'DeltaTeam'
                ELSE Team
           END AS TeamName,
           SUM(Projects)
FROM       YourTable
GROUP BY   CASE
                WHEN Team IN ('Team1', 'Team6', 'Team7') THEN 'DeltaTeam'
                ELSE Team
           END

You can see an interactive example here.

Answer 2

User-1716253493 posted

Add deltateam table or deltateam field or deltateam calculated field then query based the column

The calculated field case when deltateam=1 then 'deltateam' else team end