[Solved] how to get sum(col2) as somename,(col2*col3)/Sum(col2) as somename1 for the some date

by


You can achieve what you’re after like below:

CREATE TABLE SampleTable
    ([col1] varchar(1), [col2] int, [col3] int, [date] varchar(16))
;

INSERT INTO SampleTable
    ([col1], [col2], [col3], [date])
VALUES
    ('a', 11, 0, '3/6/2015:0:00:00'),
    ('b', 5, 4, '3/6/2015:0:00:00'),
    ('c', 5, 5, '3/6/2015:0:00:00'),
    ('d', 3, 0, '3/6/2015:0:00:00'),
    ('e', 21, 21, '3/6/2015:0:00:00')
;

SELECT t2.SumCol2, 
       sum(t1.col2 * t1.col3) / (t2.SumCol2 * 1.0) AS CalculatedColumn, 
       t1.[date] AS [Date]
FROM SampleTable t1
INNER JOIN (
    SELECT SUM(col2) AS SumCol2, [Date] 
    FROM SampleTable 
    GROUP BY [date]) AS t2 ON t1.[date] = t2.[date]
GROUP BY t2.SumCol2, t1.[date]

However this produces output that doesn’t match yours, due to a rounding issue:

SumCol2  CalculatedColumn   Date
--------------------------------------------
45       10.80              3/6/2015:0:00:00

But looking at your figures, I think my output is correct:

11 * 0   = 0
5  * 4   = 20
5  * 5   = 25 
3  * 0   = 0 
21 * 21  = 441
==================
45       / 486   =  10.8

solved how to get sum(col2) as somename,(col2*col3)/Sum(col2) as somename1 for the some date