The task sounds like this:
Choose the top 10 cities for the next store opening
Columns: City | A priority
Priority is defined as the number of buyers in the city
There should be no shop in the city.
Preliminary wrong decision:
SELECT
City,
COUNT(1) as [Priority]
FROM
Sales.vIndividualCustomer
GROUP BY City
EXCEPT
SELECT
City,
COUNT(1) as [Priority]
FROM
Purchasing.vVendorWithAddresses
GROUP BY City
ORDER BY [Priority] DESC
GO
Result:
Uniqueness disappears as soon as I start counting priority. Maybe there is another way?
PS: Used as a database AdventureWorks2016 from Microsoft.
>Solution :
You can use a NOT EXISTS
subquery
SELECT
ic.City,
COUNT(1) as [Priority]
FROM
Sales.vIndividualCustomer ic
GROUP BY
ic.City
HAVING NOT EXISTS (SELECT 1
FROM
Purchasing.vVendorWithAddresses va
WHERE
va.City = ic.City)
ORDER BY [Priority] DESC;
Note that if you wish to refer to aggregated columns then the
NOT EXISTS
will have to be in aWHERE
not aHAVING