SQL View如何分组和求和

Question

我有一个条目表和一个成员表，我想根据成员名称对所有条目求和。我已经创建了一个 View 来执行此操作，但我在尝试使语法正确时遇到了麻烦。

CREATE VIEW [dbo].[Members_View] AS
  SELECT Members.ID, Members.Name, Members.Email, 
  (SELECT COUNT(*) WHERE AssignedTo = Members.Name) as ECount
  From Members JOIN dbo.Entries ON Members.[Name] = Entries.[AssignedTo]
  Group By
    Name,
    Members.ID,
    Members.Email,
    Entries.AssignedTo

如果我删除分组依据，我只需在新的 ECount 列中为每个条目获得数字 1，但每个名称的倍数。一旦我分组，我每个名字只有一个，但每个条目仍然只有 1 个计数。如何分组并求和？

Answer 1

我想你可以做一个简单的聚合查询，省略 Entries.AssignedTo:

CREATE VIEW [dbo].[Members_View] AS
  SELECT Members.ID, Members.Name, Members.Email, 
         COUNT(*) as ECount
  From Members JOIN
       dbo.Entries
       ON Members.[Name] = Entries.[AssignedTo]
  Group By
    Name,
    Members.ID,
    Members.Email;