Statement with Joins and Aggregate Count

RWBRADLEY

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.

nescafe

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?

RWBRADLEY

Agent<---TeamRefernce --->Team<---TeamReference--->Item

RWBRADLEY

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.

nescafe

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() }

RWBRADLEY

thank you