Home

Statement with Joins and Aggregate Count

I have 3 tables: Agents, Teams, and Items
Agents { AgentReference, AgentName, TeamReference }
Teams { TeamReference, TeamName }
Items { ApplicationReference, TeamReference, DateDeleted }
I want to create a query that gives me a List by each Agent of
{ AgentReference, TeamReference, COUNT(ApplicationReferences)}

Any help would be greatly appreciated.

Comments

  • How are these tables related? Is TeamReference a foreign key to Teams? Does Team has a self-reference? Do you want to count all ApplicationReferences or unique values? Is DateDeleted relevant to the selection?

    Any visualisation would help. What is your end goal? Do you want a SQL query or do you need LINQ? Query Syntax or Method Syntax? Do you want to edit data, export or just view?
  • edited September 2018
    Agent<---TeamRefernce --->Team<---TeamReference--->Item
  • I sorry, I should have been more clear.

    Yes, they are table related.
    Yes, TeamReference is the FKey in Agents and Items table.
    TeamReference is the Primary Key in Teams table.
    Team does not have a self-reference.

    I'm trying to get a count of all ApplicationReferences that belong to the Agent, along with the Agents TeamReference.

    DateDeleted must equal a null for the application to get counted. Sorry I missed this one.

    The goal to provide a count of application from each Agent that can be rolled up to for the Team total

    If you have a better idea on how to solution this problem, please let me know.

    Thank you again.
  • Not sure if this answers your question, but it may give you something to start with.

    Table layout:
    CREATE TABLE Teams
    (
      TeamReference INT IDENTITY PRIMARY KEY NOT NULL,
      TeamName VARCHAR(128) NOT NULL
    )
    
    CREATE TABLE Agents
    (
      AgentReference INT IDENTITY PRIMARY KEY NOT NULL,
      AgentName VARCHAR(128) NOT NULL,
      TeamReference INT NULL
    )
    
    CREATE TABLE Items
    (
      ApplicationReference INT IDENTITY PRIMARY KEY NOT NULL,
      TeamReference INT NULL,
      DateDeleted DATETIME NULL
    )
    Query:
    from a in Agents
    join t in Teams on a.TeamReference equals t.TeamReference
    join i in Items on t.TeamReference equals i.TeamReference
    where i.DateDeleted == null
    group i.ApplicationReference by new { a.AgentReference, t.TeamReference } into grp
    select new { grp.Key.AgentReference, grp.Key.TeamReference, Count = grp.Count() }
    You may also have foreign keys in database:
    ALTER TABLE Agents ADD FOREIGN KEY ( TeamReference ) REFERENCES Teams ( TeamReference )
    ALTER TABLE Items ADD FOREIGN KEY ( TeamReference ) REFERENCES Teams ( TeamReference )
    Which makes joining somewhat easier:
    from t in Teams
    from a in t.Agents
    from i in t.Items
    where i.DateDeleted == null
    group i.ApplicationReference by new { a.AgentReference, t.TeamReference } into grp
    select new { grp.Key.AgentReference, grp.Key.TeamReference, Count = grp.Count() }
Sign In or Register to comment.