Q:

SQL group by on nested select statement

My data looks like this and here's google spread sheet 

I want all the rows selected, along with an added extra row which is life of item (per item_code) in terms of weeks i.e. DATEDIFF(day, min(txn_date), txn_date)/7
I tried something like this:
SELECT txn_date, txn_qty, item_code, 
         ( SELECT  DATEDIFF(day, min(txn_date), txn_date)/7
                FROM my_table
                WHERE item_code like 'X6%'
                GROUP BY item_code
            )  weeks_life
          FROM my_table
         WHERE item_code like 'X6%'

as for output, it should look like this:
 

A:

I would use DATEDIFF and a MAX OVER clause for this. 
declare @TEMP table (transaction_id int, txn_date date, txn_qty int, code char(5))

INSERT INTO @TEMP VALUES ( 1,'2016-09-20',1,'X6113')
INSERT INTO @TEMP VALUES ( 2,'2016-09-22',4,'X6113')
INSERT INTO @TEMP VALUES ( 3,'2016-11-08',7,'X6117')
INSERT INTO @TEMP VALUES ( 4,'2016-12-10',3,'X6117')
INSERT INTO @TEMP VALUES ( 5,'2016-12-22',1,'X6112')
INSERT INTO @TEMP VALUES ( 6,'2017-01-19',2,'X6118')
INSERT INTO @TEMP VALUES ( 7,'2017-02-11',4,'X6119')
INSERT INTO @TEMP VALUES ( 8,'2016-06-30',1,'X6117')
INSERT INTO @TEMP VALUES ( 9,'2016-08-03',5,'X6110')
INSERT INTO @TEMP VALUES (10,'2016-09-11',7,'X6118')
INSERT INTO @TEMP VALUES (11,'2016-10-29',1,'X6110')
INSERT INTO @TEMP VALUES (12,'2016-11-12',335,'X6113')
INSERT INTO @TEMP VALUES (13,'2017-01-06',1,'X6110')
INSERT INTO @TEMP VALUES (14,'2017-02-06',12,'X6113')

select transaction_id
      ,txn_date
      ,DATEDIFF(WEEK,MIN(txn_date) OVER(PARTITION BY code order by code),txn_date)  as life_weeks
      ,txn_qty
      ,code
from @TEMP

References from MSDN

MAX (Transact-SQL)
OVER Clause (Transact-SQL)
DATEDIFF (Transact-SQL)

