CODEWITHSUNDEEP
sqlsql-servert-sql
mitchellJ
mitchellJ

Reputation: 734

SQL View how to Group By and Sum

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?

Upvotes: 4

Views: 18073

Answers (2)

Danny
Danny

Reputation: 1759

You could count the entries in a sub-select and then join it to the main table

CREATE VIEW [dbo].[Members_View] AS 
SELECT Members.ID, Members.Name, Members.Email, entries.ECount
    FROM Members
    JOIN (SELECT AssignedTo, COUNT(*) AS ECount FROM Entries GROUP BY AssignedTo) entries ON entries.AssignedTo = Members.Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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;

Upvotes: 4

Related Questions