Calcul des moyennes dans SQL Server sans ignorer les valeurs nulles

J'ai une requête comme ci-dessous:

DECLARE @t TABLE ( EmpName VARCHAR(10) , Qty INT , Item VARCHAR(12) ) INSERT INTO @t VALUES ('Jane',3,'Dog') , ('Carle',1,'Cat') , ('Abay',5,'Goat') , ('Jane',1,'Dog') , ('Carle',10,'Cat') , ('Jane',2,'Dog') , ('Jane',8,'Goat') , ('Jane',3,'Ram') , ('Carle',2,'Dog') --SELECT * FROM @t SELECT EmpName, [Dog], [Cat], [Goat], [Ram] FROM (SELECT EmpName, Qty, Item FROM @t) AS b PIVOT(SUM(Qty) FOR Item IN ([Dog], [Cat], [Goat], [Ram])) AS p 

Et le résultat est comme vu dans la capture d'écran ci-dessous:

entrez la description de l'image ici

Je veux calculer la Qté moyenne sur l'élément sans ignorer les valeurs nulles dans le calcul. Par exemple, dans la rangée 1, EmpName Abay devrait être divisé par 5 (nombre de colonnes), comme on le voit dans cette capture d'écran:

entrez la description de l'image ici

Comment puis-je get la colonne moyenne?

Je ne suis pas vraiment familier avec la requête PIVOT , donc voici une alternative utilisant l'agrégation conditionnelle:

 SELECT Empname, Dog = SUM(CASE WHEN Item = 'Dog' THEN Qty ELSE 0 END), Cat = SUM(CASE WHEN Item = 'Cat' THEN Qty ELSE 0 END), Goat = SUM(CASE WHEN Item = 'Goat' THEN Qty ELSE 0 END), Ram = SUM(CASE WHEN Item = 'Ram' THEN Qty ELSE 0 END), Average = SUM(ISNULL(Qty, 0))/ 4.0 FROM @t GROUP BY EmpName; 

Notez que ceci ne fonctionnera que si vous n'avez que 4 Item . Sinon, vous devez recourir au tableau croisé dynamic.

DÉMO EN LIGNE


Pour le tableau croisé dynamic, j'ai utilisé une table temporaire au lieu d'une variable de table:

 DECLARE @sql NVARCHAR(MAX) = ''; SELECT @sql = 'SELECT Empname' + CHAR(10); SELECT @sql = @sql + ' , SUM(CASE WHEN Item = ''' + Item + ''' THEN Qty ELSE 0 END) AS ' + QUOTENAME(Item) + CHAR(10) FROM ( SELECT DISTINCT Item FROM #t ) t; SELECT @sql = @sql + ' , SUM(ISNULL(Qty, 0)) / (SELECT COUNT(DISTINCT Item) * 1.0 FROM #t) AS [Average]' + CHAR(10) + 'FROM #t GROUP BY EmpName;'; 

DÉMO EN LIGNE

Essayez une combinaison d' AVG et d' ISNULL , c'est-à-dire AVG(ISNULL(Dog, 0)) .

Une méthode simple est:

 select empname, goat, cat, dog, ram, (coalesce(goat, 0) + coalesce(cat, 0) + coalesce(dog, 0) + coalesce( ram, 0) ) / 4.0 as average from t; 

Une autre méthode simple utilise outer apply :

 select t.*, v.average from t outer apply (select avg(coalesce(x, 0)) from (values (t.goat), (t.cat), (t.dog), (t.ram) ) v(x) ) v(average); 
  DECLARE @t TABLE ( EmpName VARCHAR(10) , Qty INT , Item VARCHAR(12) ) INSERT INTO @t VALUES ('Jane',3,'Dog') , ('Carle',1,'Cat') , ('Abay',5,'Goat') , ('Jane',1,'Dog') , ('Carle',10,'Cat') , ('Jane',2,'Dog') , ('Jane',8,'Goat') , ('Jane',3,'Ram') , ('Carle',2,'Dog') SELECT EmpName , [Dog] , [Cat] , [Goat] , [Ram] ,p.total/4.0 as av FROM (SELECT EmpName, Qty, Item,SUM(qty)OVER(PARTITION BY EmpName) AS total FROM @t) AS b PIVOT(SUM(Qty) FOR Item IN([Dog],[Cat],[Goat],[Ram])) AS p 
 EmpName Chien Chat Chèvre Ram av
 ---------- ----------- ----------- ----------- ------- ---- ---------------------------------------
 Abay NULL NULL 5 NULL 1.250000
 Carle 2 11 NULL NULL 3.250000
 Jane 6 NULL 8 3 4.250000

V2: Script dynamic:

  CREATE TABLE #t ( EmpName VARCHAR(10) , Qty INT , Item VARCHAR(12) ) INSERT INTO #t VALUES ('Jane',3,'Dog') , ('Carle',1,'Cat') , ('Abay',5,'Goat') , ('Jane',1,'Dog') , ('Carle',10,'Cat') , ('Jane',2,'Dog') , ('Jane',8,'Goat') , ('Jane',3,'Ram') , ('Carle',2,'Dog') INSERT #t ( EmpName, Qty, Item )VALUES('Abay',100,'abc') DECLARE @cols VARCHAR(max),@sql VARCHAR(MAX),@cnt INT SELECT @cols=ISNULL(@cols+',[','[')+Item+']',@cnt=ISNULL(@cnt+1,1) FROM #t GROUP BY Item PRINT @cols PRINT @cnt SET @sql='SELECT EmpName, '+@cols+',p.total*1.0/'+LTRIM(@cnt)+' as av'+CHAR(13) +' FROM (SELECT EmpName, Qty, Item,SUM(qty)OVER(PARTITION BY EmpName) AS total FROM #t) AS b'+CHAR(13) +' PIVOT(SUM(Qty) FOR Item IN('+@cols+')) AS p' EXEC(@sql) 
 EmpName abc Chat Chèvre Chèvre Ram av
 ---------- ----------- ----------- ----------- ------- ---- ----------- ----------------------------------- ----
 Carle NULL 11 2 NULL NULL 2.600000
 Jane NULL NULL 6 8 3 3.400000
 Abay 100 NULL NULL 5 NULL 21.000000

Évitez NULL de votre phrase pivot et calculez AVG.

 ;with ct as ( SELECT EmpName , ISnull([Dog],0) Dog , ISnull([Cat],0) Cat , ISnull([Goat],0) Goat , ISnull([Ram],0) Ram FROM (SELECT EmpName, Qty, Item FROM @t) AS b PIVOT(SUM(Qty) FOR Item IN([Dog],[Cat],[Goat],[Ram])) AS p ) select empname, avg(dog) dog, avg(cat) cat, avg(goat) goat, avg(ram) ram from ct group by empname; +---------+-----+-----+------+-----+ | empname | dog | cat | goat | ram | +---------+-----+-----+------+-----+ | Abay | 0 | 0 | 5 | 0 | +---------+-----+-----+------+-----+ | Carle | 2 | 11 | 0 | 0 | +---------+-----+-----+------+-----+ | Jane | 6 | 0 | 8 | 3 | +---------+-----+-----+------+-----+ 
 SELECT EmpName , [Dog] , [Cat] , [Goat] , [Ram] ,(isnull(p.cat,0)+isnull(p.dog,0)+isnull(p.Goat,0)+isnull(p.Ram,0))/4.0 as average FROM (SELECT EmpName, Qty, Item FROM @t) AS b PIVOT(SUM(Qty) FOR Item IN([Dog],[Cat],[Goat],[Ram])) AS p