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:
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:
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