CODEWITHSUNDEEP
sql-serversubquery
IspeakComputerLanguagesSometim
IspeakComputerLanguagesSometim

Reputation: 13

SQL Query Nested Select Syntax

I am trying to create a query that provides me with the total number of Agents (AgentID) for each OfficeID. If someone could please guide me in the right direction, also if there are resources that give you a bunch of examples of different types of queries that would be useful for the future!

My issue right now is the syntax. I'm not sure where things need to go in order to get the desired output above.

Here's what I have as of now:

Tables OFFICE and AGENT:

CREATE TABLE OFFICE
(
     OfficeID NVARCHAR(5) UNIQUE,
     OfficeAddress NVARCHAR(18) NOT NULL,
     PRIMARY KEY(OfficeID)
)
GO

CREATE TABLE AGENT
(
     AgentID NVARCHAR(8) UNIQUE,
     OfficeID NVARCHAR(5) NOT NULL,
     AgentType NVARCHAR(9) NOT NULL,
     AgentFName NVARCHAR(10) NOT NULL,
     PRIMARY KEY (AgentId),
     FOREIGN KEY (OfficeID) REFERENCES OFFICE
             ON DELETE CASCADE
             ON UPDATE CASCADE
)
GO

Query:

SELECT
    OFFICE.OfficeID
FROM 
    OFFICE,
    (SELECT COUNT(AgentID)
     FROM AGENT, OFFICE 
     WHERE OFFICE.OfficeID = AGENT.OfficeID
     GROUP BY AGENT.OfficeID) 
ORDER BY 
    OFFICE.OfficeID

Upvotes: 0

Views: 9952

Answers (3)

Dale K
Dale K

Reputation: 27448

Something like this (your desired output appears to be missing):

SELECT O.OfficeID
  , (
    SELECT COUNT(*)
    FROM AGENT A
    WHERE A.OfficeID = O.OfficeID
)
FROM OFFICE O
ORDER BY O.OfficeID

Note the use of the table alias which is a recommended practice to keep your queries concise.

Upvotes: 0

probin anand
probin anand

Reputation: 134

You need to be specific with what you want as per what I think no complex query is required in your case. For example you can get your desired output from the below query 

select officeid, count(1) as NoofAgents
from Agents 
group by officeid

SQL can give you your desired way in a lot way and you can choose them based on the optimized solution.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416141

I'd do this with a JOIN and GROUP BY, no nesting required or wanted:

SELECT o.OfficeID, COUNT(a.AgentID) NumberOfAgents
FROM Office o
LEFT JOIN Agents a ON a.OfficeID = o.OfficeID
GROUP BY o.OfficeID

Upvotes: 2

Related Questions