SQL Query Nested Select Syntax

Question

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

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Dec 5, 2018 at 6:11

Joel Coehoorn · Accepted Answer · 2018-12-05 04:01:15Z

2

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

answered Dec 5, 2018 at 4:01

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2020-06-08 03:33:26Z

0

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.

edited Jun 8, 2020 at 3:33

answered Dec 5, 2018 at 3:47

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Comments

Dale K · Accepted Answer · 2020-06-08 03:31:35Z

0

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.

edited Jun 8, 2020 at 3:31

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 5, 2018 at 4:37

probin anand

1347 bronze badges

1 Comment

Joel Coehoorn Over a year ago

I did think about this before posting my answer, but I decided to keep the join in case of an office with no agents, where the OP might still want a 0 result.

Collectives™ on Stack Overflow

SQL Query Nested Select Syntax

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related