how to use with clause results as a temporary table +----+-----------+--------------+-----------+
| id | file_name |  file_value  | file_type |
+----+-----------+--------------+-----------+
|  1 | file1     |  8,624       | 1         |
|  2 | file2     |  0,025       | 1         |
|  3 | file3     |  1,005       | 1         |
|  4 | file4     |  0,655       | 2         |
|  5 | file5     |  1,251       | 2         |
|  6 | file6     |  0,148       | 2         |
|  7 | file7     |  1,146       | 3         |
|  8 | file8     |  2,485       | 3         |
|  9 | file9     |  0,512       | 3         |
+----+-----------+--------------+-----------+

+--------+    
| lineal |    
+--------+    
; WITH vw AS 
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

SELECT vw2.* , vwt.* FROM vw AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM vw AS cavw 
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;

+----------------+    
| this IS faster |    
+----------------+ 
; WITH vw AS 
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

 SELECT * INTO #example FROM vw --or @example AS table...

SELECT vw2.* , vwt.* FROM #example AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM #example AS cavw 
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;


Any way to optimize speed for firt query?
You can write this query without using a CTE or temp table:

SELECT id, 
       UPPER(file_name) [Name], 
       CAST(file_value AS INT) [Value], 
       SUM(CAST(file_value AS INT)) OVER (PARTITION BY file_type) AS type_total, 
       AVG(CAST(file_value AS FLOAT)) OVER (PARTITION BY file_type) AS type_avg
FROM test_example
WHERE id % 2 = 0


Is that any better?