I have a table like below

I would like to transform this table into the following table

With calculation: for each element of column1, SUM (column2) / count of (non-zero column2)

The only thing I can think of is looping through distinct elements of Column1 and run:

SELECT SUM(Column2) 
FROM Table 
WHERE Column1 = i / (SELECT COUNT(Column2) 
                     FROM Table 
                     WHERE Column1 = i AND Column2 <> 0)

and generate a table.

Is there a better way of doing this?

CodePudding user response：

Use aggregation:

SELECT Column1,
       SUM(Column2) / COUNT(CASE WHEN Column2 <> 0 THEN 1 END) AS Column2
FROM yourTable
GROUP BY Column1;

CodePudding user response：

You can go for derived table to filter out the 0 column2 rows. Then, you can apply GROUP BY.

declare @table table (Column1 char(1),  Column2 int)
insert into @table values
('A',200),
('A',200),
('A',0  ),
('B',300),
('B',200),
('C',100);

SELECT Column1, (sum(column2) / count(column2) ) as column2
from
(
SELECT * FROM @TABLE where Column2 <> 0) as t
group by Column1

CodePudding user response：

You can use where clause to remove rows with 0 in column2 then use aggregation to have your desired result. But it will remove those column1 values which have 0 in all columnd2.

But Query2 will return rows with zero values in column2 instead of removing the removing the row.

Schema and insert statements:

 create table testTable (Column1    varchar(50), Column2 int);
 insert into testTable values('A',  200);
 insert into testTable values('A',  200);
 insert into testTable values('A',  0);
 insert into testTable values('B',  300);
 insert into testTable values('B',  200);
 insert into testTable values('C',  100);
 insert into testTable values('D',  0);

Query1:

 SELECT Column1,
        SUM(Column2) / COUNT(*) AS Column2
 FROM testTable where column2<>0
 GROUP BY Column1;

Output:

Query2:

 SELECT Column1,
        Coalesce(SUM(Column2) / nullif(COUNT(CASE WHEN Column2 <> 0 THEN 1 END),0),0) AS Column2
 FROM testTable
 GROUP BY Column1;

Output:

db<>fiddle here