I have a table that looks like this.

I would like to create an alias project (let's say project 23) that sums up values of Cost and Balance columns for projects 2 and 3 only. Is there a way to do that in SQL Server?

I expect this to be the outcome.

CodePudding user response：

I think you have wrongly put 55 in column 2 instead of 45 but you can achieve this result using UNION ALL syntax -

SELECT project, Cost, Balance
  FROM YOUR_TABLE
SELECT 23 AS project, SUM(Cost), SUM(Balance)
  FROM YOUR_TABLE
 WHERE project IN (2, 3);

CodePudding user response：

I don't know if grouping sets are really the right tool for your problem. You can generate a solution using them though:

select
    coalesce(project, max(case when project in (2, 3) then 23 end)) as project,
    sum(cost), sum(balance)
from Projects
group by grouping sets (case when project in (2, 3) then 23 end, project)
having grouping(project) = 0 or max(case when project in (2, 3) then 23 end) is not null

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ce2d7510a5cc926d03b1f0b0e51d65c1

If this is really just a one-off for only these two project numbers it's probably easier to do the simple union:

select project, cost, balance
from Projects
union all
select 23, sum(cost), sum(balance)
from Projects
where project in (2, 3);