Statement with Joins and Aggregate Count

RWBRADLEY · September 2018

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 · September 2018

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 · September 2018

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

RWBRADLEY · September 2018

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 · September 2018

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 · September 2018

thank you

Statement with Joins and Aggregate Count

Comments

Categories