MS SQL Server
  1. MS SQL Server
  3. Ne pas get les valeurs SUM correctes dans SQL Server 2012 lors de l'utilisation d'un PIVOT
Intereting Posts
La database SQL Server piratée a besoin d'une regex Différence entre l'index composite non-cluster et l'arborescence d'index non-cluster – difrence Sélection de la date maximale à partir de plusieurs tables AWS RDS SQL Server impossible de supprimer la database Migration de données entre différents SGBD Synchronisation des connections avec des passwords entre les servers SQL Ordre des valeurs décimales dans Sql Server 2008 Justifications de l'utilisation de MSSQL sur MySQL du sharepoint vue des coûts Sélectionnez uniquement les parents avec un enfant TSQL: Comment get la list des groupes auxquels un user appartient dans Active Directory Comment get tous les nombres entre une gamme Est-il possible d'parsingr un set de données par un nom SQL aliasé? Meilleure pratique d'index sur une table contenant une grande quantité de données Comparaison NULL Comment me connecter à une database MSSQL en utilisant le module DBI de Perl dans Windows?

Ne pas get les valeurs SUM correctes dans SQL Server 2012 lors de l'utilisation d'un PIVOT

J'essaye de créer une requête qui pivotera au-dessus de quelques rangées mais SUM quelques colonnes et puis les grouper set. J'ai déjà utilisé la fonction PIVOT mais je rencontre des problèmes lorsque mon jeu de résultats contient des valeurs similaires.

C'est SQL Server 2012.

Exemple de code: 

 CREATE TABLE #Foo ( Store varchar(50), Employee varchar(50), Sold money, Waste money, Tmsp datetime ) INSERT INTO #Foo VALUES ('Harrisburg', 'John', 20.00, 10.00, GETDATE()), ('Harrisburg', 'John', 20.00, 10.00, GETDATE()), ('Harrisburg', 'Jim', 20.00, 10.00, GETDATE()), ('Seattle', 'Jim', 20.00, 10.00, GETDATE()), ('Seattle', 'Alex', 20.00, 10.00, GETDATE()) SELECT Store, SUM(Sold) TotalSold, SUM([John]) WastedByJohn, SUM([Jim]) WastedByJim, SUM([Alex]) WastedByAlex FROM #Foo PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT GROUP BY Store DROP TABLE #Foo

Cela donne les résultats suivants: 

 Store | TotalSold | WastedByJohn | WastedByJim | WastedByAlex Harrisburg | 20.00 | 20.00 | 10.00 | NULL Seattle | 20.00 | NULL | 10.00 | 10.00

Le TotalSold pour Harrisburg ne devrait-il pas être de 60,00 et le TotalSold pour Seattle de 40,00 sur la base des données du tableau?

Il devient plus difficile pour moi de comprendre, parce que si je change datatables pour que les valeurs ne soient pas les mêmes, j'obtiens les bons résultats. 

 INSERT INTO #Foo VALUES ('Harrisburg', 'John', 25.00, 10.00, GETDATE()), ('Harrisburg', 'John', 30.00, 10.00, GETDATE()), ('Harrisburg', 'Jim', 40.00, 10.00, GETDATE()), ('Seattle', 'Jim', 50.00, 10.00, GETDATE()), ('Seattle', 'Alex', 60.00, 10.00, GETDATE())

Cet set de données donne le résultat attendu: 

 Store | TotalSold | WastedByJohn | WastedByJim | WastedByAlex Harrisburg | 95.00 | 20.00 | 10.00 | NULL Seattle | 110.00 | NULL | 10.00 | 10.00

J'ai regardé un peu autour de moi et je n'ai pas pu find de réponse à la question de savoir pourquoi le PIVOT serait différent en fonction de valeurs distinctes en ce qui concerne l'agrégation. J'ai l'printing qu'il y a quelque chose de fondamental qui me manque ici, à less que je ne sois confronté à un problème avec SQL Server, ce qui est peu probable.

Toute aide serait grandement appréciée.

Merci!

La requête suivante devrait vous donner ce que vous voulez: 

 SELECT Store, TotalSold, [John] AS WastedByJohn, [Jim] AS WastedByJim, [Alex] AS WastedByAlex FROM (SELECT Store, Employee, Waste, SUM(Sold) OVER (PARTITION BY Store) AS TotalSold FROM #Foo) src PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT

Pour comprendre pourquoi vous obtenez des résultats inattendus, essayez votre requête sans la clause GROUP BY : 

 SELECT Store, Sold, [John], [Jim], [Alex] FROM #Foo PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT

Sortie: 

 Store Sold John Jim Alex Harrisburg 20,00 20,00 10,00 NULL Seattle 20,00 NULL 10,00 10,00

Maintenant, essayez la même chose avec la deuxième version des données d'exemple:

Sortie: 

 Store Sold John Jim Alex Harrisburg 25,00 10,00 NULL NULL Harrisburg 30,00 10,00 NULL NULL Harrisburg 40,00 NULL 10,00 NULL Seattle 50,00 NULL 10,00 NULL Seattle 60,00 NULL NULL 10,00

En comparant les 2 sets de résultats différents, vous pouvez clairement voir que PIVOT a lieu pour chaque combinaison de colonnes qui n'y participent pas, c'est-à-dire pour chaque combinaison de Store , Sold .

Dans le premier cas, il n'y a que Harrisburg,20,00 et Seattle,20,00 . C'est pourquoi vous n'obtenez que deux rangées dans ce cas. Dans le second cas, vous avez un total de 3 + 2 = 5 combinaisons.

Vous pouvez maintenant voir pourquoi GROUP BY ne fonctionne que dans le second cas.

Vous n'obtenez pas ce que fait l'instruction pivot. Laisse-moi expliquer. Tout d'abord il y a 3 éléments: propagation, agrégation et groupement. L'épandage est celui que vous obtenez dans les colonnes, c'est-à-dire Employee IN ([John], [Jim], [Alex]) . L'agrégation est SUM(Waste) . Alors, quel est l'élément de regroupement? Le dernier est déterminé par l'élimination des colonnes. C'est à dire chaque colonne, mais l'agrégation et l'étalement. Dans votre exemple, il s'agira de Store, Sold, Tps . Il va regrouper datatables par ces 3 colonnes. Mais tu ne veux pas ça. Vous souhaitez regrouper uniquement par Store . Alors que faire? Je peux suggérer d'utiliser l'agrégation conditionnelle: 

 SELECT Store, SUM(Sold) TotalSold, SUM(CASE WHEN Employee = 'John' THEN Waste ELSE 0 END) WastedByJohn, SUM(CASE WHEN Employee = 'Jim' THEN Waste ELSE 0 END) WastedByJim, SUM(CASE WHEN Employee = 'Alex' THEN Waste ELSE 0 END) WastedByAlex FROM #Foo GROUP BY Store