DECLARE @table TABLE (ID INT,VALUE INT)
INSERT INTO @table
( ID, VALUE )
VALUES ( 1, -- ID - int
1 -- VALUE - int
)
INSERT INTO @table
( ID, VALUE )
VALUES ( 1, -- ID - int
2 -- VALUE - int
)
INSERT INTO @table
( ID, VALUE )
VALUES ( 1, -- ID - int
0 -- VALUE - int
)
SELECT id,SUM(VALUE) 'Sum',COUNT(VALUE) 'Count',COUNT(CASE value WHEN 0 THEN NULL ELSE value end) 'Count_NoNull_NoZero',AVG(VALUE)*1.0 'AVG',SUM(VALUE)*1.0/COUNT(CASE value WHEN 0 THEN NULL ELSE value end)*1.0 'AVG_NoNull_Nozero' FROM @table AS t
GROUP BY id
