CODEWITHSUNDEEP
sql-server
0nir
0nir

Reputation: 1355

Add the column's with Alias name in sql server

I have the below query -

  select TeamProjectSK,     
  sum(case when d.System_State = 'Proposed' then 1 else 0 end) as New,
  sum(case when d.System_State = 'Active' then 1 else 0 end) as Active,
  sum(case when d.System_State = 'Resolved' then 1 else 0 end) as Resolved,
  sum(case when d.System_State = 'Closed' then 1 else 0 end) as Closed     
  from
  (
   select w1.System_Id, w1.TeamProjectSK, w1.System_State, w1.System_Rev,
   row_number() over(partition by w1.System_Id, w1.TeamProjectSK, w1.System_State order by w1.System_Rev desc) rn
   from dbo.DimWorkItem w1       
  ) d
   where rn = 1
   and d.System_Rev = (select max(w2.System_Rev) from dbo.DimWorkItem w2 where w2.System_Id =   d.System_Id)
   group by TeamProjectSK
   order by TeamProjectSK desc;

The result of the query looks like -

TeamProjectSK   New Active  Resolved    Closed
157           14    115     1           169
156           0     0       0           0
155           0     0       0           0
154           0     0       0           0
151           2     1       0           1

Now i want a column "Total_Count" which should be the sum of New+Active+Resolved+Closed. The output should look like -

TeamProjectSK  Total_Count  New Active  Resolved    Closed
157      289            14  115 1   169
156      0              0   0   0   0
155      0              0   0   0   0
154      0              0   0   0   0
151      4              2   1   0   1

I tried the below query, but it would not help. Please look into the same.

    select TeamProjectSK, 
    New + Active + Resolved + Closed as Total_Count,    
    sum(case when d.System_State = 'Proposed' then 1 else 0 end) as New,
    sum(case when d.System_State = 'Active' then 1 else 0 end) as Active,
    sum(case when d.System_State = 'Resolved' then 1 else 0 end) as Resolved,
    sum(case when d.System_State = 'Closed' then 1 else 0 end) as Closed     
    from
    (
    select w1.System_Id, w1.TeamProjectSK, w1.System_State, w1.System_Rev,
     row_number() over(partition by w1.System_Id, w1.TeamProjectSK, w1.System_State order by w1.System_Rev desc) rn
     from dbo.DimWorkItem w1   

    ) d
     where rn = 1
     and d.System_Rev = (select max(w2.System_Rev) from dbo.DimWorkItem w2 where w2.System_Id = d.System_Id)
     group by TeamProjectSK
     order by TeamProjectSK desc;

Regards.

Upvotes: 0

Views: 128

Answers (1)

bummi
bummi

Reputation: 27384

select TeamProjectSK, New + Active + Resolved + Closed as Total_Count, New Active, Resolved, Closed
from
(
select TeamProjectSK,     
  sum(case when d.System_State = 'Proposed' then 1 else 0 end) as New,
  sum(case when d.System_State = 'Active' then 1 else 0 end) as Active,
  sum(case when d.System_State = 'Resolved' then 1 else 0 end) as Resolved,
  sum(case when d.System_State = 'Closed' then 1 else 0 end) as Closed     
  from
  (
   select w1.System_Id, w1.TeamProjectSK, w1.System_State, w1.System_Rev,
   row_number() over(partition by w1.System_Id, w1.TeamProjectSK, w1.System_State order by w1.System_Rev desc) rn
   from dbo.DimWorkItem w1       
  ) d
   where rn = 1
   and d.System_Rev = (select max(w2.System_Rev) from dbo.DimWorkItem w2 where w2.System_Id =   d.System_Id)
   group by TeamProjectSK
) a
   order by TeamProjectSK desc

Upvotes: 1

Related Questions