CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
br0ken.pipe
br0ken.pipe

Reputation: 910

Add a grouped value to the same table

I'm trying to add a grouped value to an existing table. I would like to add the number of all "Work Days" per Month and Year as extra columns. I tried it with a group by statement in a Sub-Select and Joining it to the same table again. It somehow works but is really time-consuming. Is there any smarter way? 

Date        Year    Quarter Month   Week    Weekday Descr.  Work_Days_Month
01.01.2017  2017    1       1       52      7       Holiday  5
02.01.2017  2017    1       1       1       1       Work Day 5
03.01.2017  2017    1       1       1       2       Work Day 5
04.01.2017  2017    1       1       1       3       Work Day 5
05.01.2017  2017    1       1       1       4       Work Day 5
06.01.2017  2017    1       1       1       5       Work Day 5
07.01.2017  2017    1       1       1       6       Weekend  5
08.01.2017  2017    1       1       1       7       Weekend  5

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use window functions:

select t.*,
       sum(case when Description = 'Work Day' then 1 else 0 end) over
           (partition by year, month) as yyyymm_workdays
from t;

Upvotes: 2

Related Questions