SQL View how to Group By and Sum

Question

I have an Entries Table and a Members table and I want to sum all of the entries based on a member name. I've created a view to do this but I'm having a terrible time trying to get the syntax correct.

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

If I remove the Group By I simply get the number 1 in my new ECount column for each entry but multiples of each name. Once I group by I only have one of each name but each entry still has only 1 count. How do I Group By AND Sum?

Answer 1

I think you can just do a simple aggregation query, leaving out 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;