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
Uniqueness disappears as soon as I start counting priority. Maybe there is another way?
PS: Used as a database AdventureWorks2016 from Microsoft.
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 EXISTSwill have to be in a