CODEWITHSUNDEEP
sqlsql-serverdatabase
Jonathan De Badrihaye
Jonathan De Badrihaye

Reputation: 392

Make subquery usage more efficient?

I have following example query:

SELECT Name, Number,
(SELECT COUNT(1) FROM (SELECT DISTINCT Street FROM tableA b WHERE a.Name = b.Name AND a.Number = b.Number AND b.Type = 'VILLA' AND b.Value IN (5, 1, 9)) x) as NumberOfStreetsForVillas,
(SELECT COUNT(1) FROM (SELECT DISTINCT Street FROM tableA b WHERE a.Name = b.Name AND a.Number = b.Number AND b.Type = 'SMALL' AND b.Value IN (1, 2, 3)) x) as NumberOfStreetsForSmallHouses,
FROM dbo.tableA a
GROUP BY Name, Number

How can I make this query more efficient?

I have read about using a SELECT COUNT(1) + subselect (which I did) instead of using a COUNT(DISTINCT), but is there a way to turn this into a more efficient query using joins?

This is just an example query, so the actually query will probably contain about 12 subqueries instead of 2.

Thanks in advance!

Upvotes: 0

Views: 145

Answers (1)

JNevill
JNevill

Reputation: 50019

This query should do the same thing. It's a little difficult to know for sure since I don't know what your data looks like, but even so... This should work:

SELECT
    a.Name, 
    a.Number, 
    Count(Distinct CASE WHEN b.type='Villa' AND b.value in (5,1,9) THEN b.street END) as NumberOfStreetsForVillas
    Count(Distinct CASE WHEN b.type='Small' AND b.value in (1,2,3) THEN b.Street END) as NumberOfCitiesForSmallHouses
FROM dbo.tableA a
    LEFT OUTER JOIN dbo.tableb b
        ON a.Name = b.Name AND a.Number = b.Number
GROUP BY a.Name, a.Number;

Upvotes: 3

Related Questions