Error when using WHERE in Correlated Subquery

Question

I have the following relationship: Bank -> Financing -> Contracts -> Supplier

I have to select all the Banks that is related to a Supplier, using the query below:

SELECT DISTINCT A.Name AS BankName, A2.Name as SupplierName
FROM Companies A
INNER JOIN Financing F ON F.IdFinancialCompany = A.Id
INNER JOIN Contracts C ON F.IdContract = C.Id
INNER JOIN Companies A2 ON C.IdSupplier = A2.Id
GROUP BY A.Name, A2.Name

So far, so good.

Now I have to list the number of contracts that are active and the number of total contracts, so I thought about including a subquery:

SELECT DISTINCT A.Name AS BankName, A2.Name as SupplierName,
(SELECT COUNT(C.Id)),
(SELECT COUNT(C.Id) WHERE C.ContractStatus = 1)
FROM Companies A
INNER JOIN Financing F ON F.IdFinancialCompany = A.Id
INNER JOIN Contracts C ON F.IdContract = C.Id
INNER JOIN Companies A2 ON C.IdSupplier = A2.Id 
GROUP BY A.Name, A2.Name

Line 2 Works well, but I got na error in line 3:

Column 'Contracts.ContractStatus' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I expect the following result:

---

BANK NAME | SUPPLIER NAME | TOTAL CONTRACTS | ACTIVE CONTRACTS

Bank 1 | Supplier 1| 5 | 2

How can I achieve this? I'm using SQL Server 2014

Thanks in advance!

Answer 1

Remove the distinct since your group by and use CASE

SELECT A.Name AS BankName, A2.Name as SupplierName,
COUNT(C.Id),
SUM(CASE WHEN C.ContractStatus = 1 THEN 1 ELSE 0 END) 
FROM Companies A
INNER JOIN Financing F ON F.IdFinancialCompany = A.Id
INNER JOIN Contracts C ON F.IdContract = C.Id
INNER JOIN Companies A2 ON C.IdSupplier = A2.Id 
GROUP BY A.Name, A2.Name