Using correlated subquery or join instead of derived table

Question

Please consider this scenario:

I have a table like this:

CityCode        CityName        Col_6        Col_9        Col_10
----------------------------------------------------------------------
001                London        10            21               1
001                London        24            13               2
001                London        39            10               2
002                Paris         19            61               1
002                Paris         10            50               1
003                Vien          12            41               2
004                Mosco         22            27               2
004                Mosco         28            9                2
004                Mosco         41            30               1
004                Mosco         33            12               1
005                Cairo         10            21               1
006                Milan         19            41               1
006                Milan         40            32               2

I want to wite a query that calculate some formula for every city. Now I use a cursor for distinct cities and then select appropriate records and do calculation:

DECLARE mycur   CURSOR  
FOR
    SELECT CityCode
    FROM tblCities 

OPEN mycur
FETCH NEXT FROM mycur INTO @param_Code  

WHILE (@@fetch_status = 0)
BEGIN
     SELECT 
         @param_Code, (SELECT COUNT(*)
                       FROM MyTable
                       WHERE col_9 = 61
                         AND (City_Code = @param_Code) 
                      ) /
                      (SELECT COUNT(*)
                       FROM MyTable
                        AND (City_Code = @param_Code) 
                      ),
         (SELECT COUNT(*)
          FROM MyTable
          WHERE col_10 = 1
            AND (City_Code = @param_Code)) /
            (SELECT COUNT(*)
             FROM MyTable
             WHERE (col_10 = 1 OR col_10 = 2)
               AND (City_Code = @param_Code)),
         Some other calculation like above

     FETCH NEXT FROM mycur INTO @param_Code
END

CLOSE mycur
DEALLOCATE mycur

@param_Code is my cursor variable.

How I can do this better? I don't want use derived table. I prefer use correlated sub query or join.

Thanks

Answer 1

Use simple GROUP BY clause with CASE expressions. Cursor in this case is excessive.

SELECT CityCode, COUNT(CASE WHEN col_9 = 61 THEN 1 END) * 1.00 / COUNT(*),
                 COUNT(CASE WHEN col_10 = 1 THEN 1 END) * 1.00 
                   / COUNT(CASE WHEN (Col10 IN (1, 2)) THEN 1 END)
FROM dbo.MyTable
GROUP BY CityCode

Demo on SQLFiddle

If there is need in further calculation then you can wrap this script in CTE:

;WITH cte AS
 (       
  SELECT CityCode, COUNT(CASE WHEN col_9 = 61 THEN 1 END) * 1.00 / COUNT(*),
                   COUNT(CASE WHEN col_10 = 1 THEN 1 END) * 1.00 
                     / COUNT(CASE WHEN (Col10 IN (1, 2)) THEN 1 END)
  FROM dbo.MyTable
  GROUP BY CityCode
  )
  SELECT --Some other calculation like above
  FROM cte

Answer 2

Possible CTE will help you -

;WITH data AS 
(
    SELECT *
    FROM dbo.MyTable t
    WHERE t.state_Code = @param_Code
)
SELECT 
    @param_Code 
    , result = (SELECT COUNT(1)
                FROM data
                WHERE col_9 = 61)
               /
               (SELECT COUNT(1)
                FROM data)