Combiner les résultats pour countr les totaux pour les individus

J'ai une table dans ma database qui garde la trace des pages envoyées aux users individuels et aux groupes. Les users font partie de groupes. Seuls les users individuels peuvent répondre aux pages. Voici le DDL pour la table:

--PageStatus 1 = Expired --PageStatus 2 = Answered --PageStatus 3 = Canceled CREATE TABLE [Pagings] ( [Id] int NOT NULL IDENTITY(1,1) , [UserProfileId] int NULL , [GroupId] int NULL , [Message] nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PageStatus] int NOT NULL , [DateCreated] datetime NULL , [DateModified] datetime NULL , [IsRecurring] bit NOT NULL DEFAULT ((0)) , [AnsweredById] int NULL , [AnsweredDateTime] datetime NULL , CONSTRAINT [PK_ft.Pagings] PRIMARY KEY ([Id]) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO 

Chaque fois que le PageStatus est Expired (1) ou Canceled (3), nous n'avons aucune donnée pour les colonnes AnsweredById ou AnsweredDateTime . Si la réponse est UserProfileId nous définissons la valeur du UserProfileId provenant de l'application dans la colonne AnsweredById de la personne qui a répondu. Si un groupe est paginé, toute personne ayant répondu à la page est supposée faire partie de ce groupe et son UserProfileId est défini dans la colonne AnsweredById .

Voici un exemple de résultat et le SqlFiddle pour accompagner datatables. entrez la description de l'image ici

J'ai besoin de comprendre comment get le nombre total de pages pour un user, y compris le groupe auquel il appartient, le nombre de pages auxquelles il a répondu et le total des groupes. Voici un exemple de ce que j'attendrais comme résultat basé sur l'set ci-dessus:

 UserId GroupId TotalPagesForUser TotalAnsweredForUser TotalPagesForGroup TotalAnsweredForGroup ------ ------- ----------------- -------------------- ------------------ ---------------------- 1 2 3 1 1 1 3 1 3 1 2 2 4 1 2 1 2 2 

J'ai essayé de joindre la table à elle-même sur les UserProfileId et AnsweredById et avec une table de Group qui existe dans la database, mais mes résultats étaient loin et je me retrouve avec beaucoup de données en double.

Je le splitais en deux parties, d'abord assembler les nombres agrégés pour les users, puis get les numéros de groupe dans une sous-requête dans le cadre de la requête principale ou deux requêtes distinctes avec les résultats étant assemblés à la fin. Quoi qu'il en soit, mon premier essai rugueux:

 Select u.Id as UserId, g.Id as GroupId, g.name as GroupName, count(0) as TotalPagesForUser, Sum(case when p.AnsweredById IS NOT NULL then 1 else 0 end) as TotalAnsweredForUser, (SELECT COUNT(0) FROM Pagings WHERE GroupId = g.Id) as TotalPagesForGroup, (SELECT COUNT(0) FROM Pagings WHERE GroupId = g.Id AND AnsweredById IS NOT NULL) as TotalAnsweredForGroup from UserProfiles u INNER JOIN Groups g on g.Id = u.GroupId INNER JOIN Pagings p on p.UserProfileId = u.Id or p.AnsweredById = u.Id GROUP BY u.Id, g.Id, g.name ORDER BY u.Id 

Bien que j'obtienne des valeurs légèrement différentes de ce que vous projetiez … mais je n'ai pas encore eu l'occasion d'examiner datatables sources en détail.