Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

TSQL request is required

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 a WHERE not a HAVING

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading