Transform SQL Server table based on calculation

user_9860690 0
Viewed 93

I have a table like below

Column1 Column2
A 200
A 200
A 0
B 300
B 200
C 100

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)

Column1 Column2
A ((200+ 200 + 0) / 2) = 200
B ((300 + 200) / 2) = 250
C 100 / 1 = 100

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?

sql-server sql
5 Answers

Use aggregation:

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

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:

Column1 Column2
A 200
B 250
C 100

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:

Column1 Column2
A 200
B 250
C 100
D 0

db<>fiddle here

user_86516010

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
Column1 column2
A 200
B 250
C 100
user_6349350

Retrieve distinct value of column1 and ignore zero value of column2 while division with total sum of column2. And also consider here divide by zero error.

-- SQL Server
SELECT t.column1
     , t.column2 / (CASE WHEN (t.total - t.total_zero) = 0 THEN 1 ELSE (t.total - t.total_zero) END)
FROM (SELECT column1
           , SUM(column2) column2
           , COUNT(CASE WHEN column2 = 0 THEN 1 END) total_zero
           , COUNT(1) total
      FROM test
      GROUP BY column1) t

Please check this url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3853456941909ffffb8792415adc1f6f

user_163646250

Use AVG() window function because you want the average value of Column2.

If you want all the values of Column1 in the results even if they have only 0s in Column2:

SELECT DISTINCT Column1,
       AVG(CASE WHEN Column2 <> 0 THEN Column2 END) OVER (PARTITION BY Column1) Column2
FROM tablename;

If you want results for the values of Column1 that have at least 1 Column2 not 0:

SELECT DISTINCT Column1,
       AVG(Column2) OVER (PARTITION BY Column1) Column2
FROM tablename
WHERE Column2 <> 0;

See the demo.

Note that SQL Server truncates the average of integers to an integer, so if you want the result as a floating point number you should multiply Column2 by 1.0, like:

AVG(CASE WHEN Column2 <> 0 THEN 1.0 * Column2 END)

or:

AVG(1.0 * Column2)
user_104988280
Related