This is a interview SQL test on which stumped me. Here is the question: In every office branch, there is a sales quota to be fulfilled by each salesperson. The SQL query is to display the number of salesperson in each branch that equal or exceed the sales quota. There are 2 tables,
Branch
and
SalesPerson
.
SalesPerson
linked to
Branch
through
BranchID
.
Branch
table has a
Quota
field to be fulfilled by each
SalesPerson
's
Sales
Field.
The SQL commands to create the tables are provided.
CREATE TABLE [dbo].[Branch](
[ID] [int] NOT NULL,
[Quota] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SalesPerson](
[ID] [int] NOT NULL,
[Name] [nchar](10) NOT NULL,
[Sales] [int] NOT NULL,
[BranchID] [int] NOT NULL
) ON [PRIMARY]
GO
The SQL commands to populate the tables are provided below.
INSERT INTO Branch (ID, Quota) VALUES (1, 500);
INSERT INTO Branch (ID, Quota) VALUES (2, 600);
INSERT INTO SalesPerson (ID, Name, Sales, BranchID) VALUES (1, 'John', 500, 1);
INSERT INTO SalesPerson (ID, Name, Sales, BranchID) VALUES (2, 'Jane', 400, 2);
What I have tried:
SELECT B.ID AS BranchNum, COUNT(*) AS EligibleSalesPersonCount FROM
SalesPerson AS S INNER JOIN Branch AS B ON (S.BranchID = B.ID)
WHERE S.Sales >= B.Quota
GROUP BY B.ID;
My Output
BranchNum EligibleSalesPersonCount
1 1
Expected Output
BranchNum EligibleSalesPersonCount
1 1
2 0
My question is how to display the result when the EligibleSalesPersonCount is 0?