Add calculated Items - SQL Server 2008R2

Question

I need to add a calculated column item in the same column. Please see my SQL code for both existing data and desired outcome.

Product O is added according to:

• for 201501 Product O = sum of en_count for product Y,W,N when yrmnth = 201501
• for 201502 Product O = sum of sum of en_count for product Y,W,N when yrmnth = 20150

Thanks,

Helal

SQL:

--Existing Data                                               
--===== If the test table already exists, drop it             
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1

--===== Create the test table with                            
CREATE TABLE #Table1
(                                                 
    product char(100),                          
    yrmnth varchar(6),                         
    en_count int,                                
    date date,                                   
)                                                 

INSERT INTO #Table1 (product, yrmnth, en_count, date)                  
   SELECT 'Y', '201501', 5000 , '01/01/2015' union all
   SELECT 'Y', '201502', 6000 , '02/01/2015' union all
   SELECT 'Z', '201501', 7000 , '01/01/2015' union all
   SELECT 'Z', '201502', 8000 , '02/01/2015' union all
   SELECT 'W', '201501', 9000 , '01/01/2015' union all          
   SELECT 'W', '201502', 10000   , '02/01/2015' union all        
   SELECT 'N', '201501', 11000   , '01/01/2015' union all        
   SELECT 'N', '201502', 12000   , '02/01/2015'                  

--Desired Outcome                                             
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2

--===== Create the test table with                            
CREATE TABLE #Table2                                          
(                                                 
    product char(100),                          
    yrmnth varchar(6),                         
    en_count int,                                
    date date,                                   
)                                                 

INSERT INTO #Table2 (product, yrmnth, en_count, date)                  
    SELECT 'Y', '201501', 5000 , '01/01/2015' union all          
    SELECT 'Y', '201502', 6000 , '02/01/2015' union all          
    SELECT 'Z', '201501', 7000 , '01/01/2015' union all          
    SELECT 'Z', '201502', 8000 , '02/01/2015' union all          
    SELECT 'W', '201501', 9000 , '01/01/2015' union all          
    SELECT 'W', '201502', 10000 , '02/01/2015' union all          
    SELECT 'N', '201501', 11000 , '01/01/2015' union all          
    SELECT 'N', '201502', 12000 , '02/01/2015' union all          
    SELECT 'O', '201501', 32000 , '01/01/2015' union all          
    SELECT 'O', '201502', 36000 , '02/01/2015'                    

select *                                                      
from #Table2

Answer 1

This looks like a simple insert ... select ... statement:

insert #Table1 (product, yrmnth, en_count,date)
select 'O', yrmnth, SUM(en_count), date
from #Table1 
group by yrmnth, date

I assume you meant to sum all products (Y,Z,W,N) and not just (Y,W,N) as the former gives the indicated sum, while the latter differs (with the missing N value). If it wasn't an oversight then add where product in ('Y','W','N') after the from clause.