How to make a ratio between values from two queries?

Question

I have these two queries

 SELECT
    COUNT(DISTINCT s.KliRC) Celkem
    ,r.Region
FROM dbo.Smlouvy s
        JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
        JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu   
GROUP BY
    r.Region

-- +

SELECT
    COUNT(DISTINCT s.KliRC) Solus
    ,r.Region
FROM dbo.SolusDetaily sd
    JOIN dbo.Smlouvy s      ON sd.CisloZadosti    = s.CisloSmlouvy    
        JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
        JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu
GROUP BY
    r.Region

And I want to make a ratio between them on the (Count) fields. I've tried UNION ALL with cte, but without desired result.

What does `ration between them (Count)` mean? Can you give examples? — Jamiec, Jul 03 '17 at 14:39
Will both queries always return the same list of `Region`s, or can one query sometimes have a `Region` that the other does not? — toonice, Jul 03 '17 at 17:01

score 1 · Answer 1 · answered Jul 03 '17 at 14:54

I think you could INNER JOIN two sub-queries.
Use NULLIF(c.Celkem,0) to avoid divide-by-zero error.

SELECT s.Region, (s.Solus * 1.0)/NULLIF(c.Celkem,0) as Ratio
FROM
(
    SELECT
            COUNT(DISTINCT s.KliRC) Solus
            ,r.Region
    FROM dbo.SolusDetaily sd
        JOIN dbo.Smlouvy s      ON sd.CisloZadosti    = s.CisloSmlouvy    
            JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
            JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu
    GROUP BY
        r.Region
) s
INNER JOIN 
(
    SELECT
            COUNT(DISTINCT s.KliRC) Celkem
            ,r.Region
    FROM dbo.Smlouvy s
            JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
            JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu   
    GROUP BY
        r.Region
) c on  s.Region = c.Region

Pரதீப் · Answer 2 · 2017-07-03T15:18:01.480

0

Try something like this.

select Region,(Celkem * 1.0) /sum(Celkem)over() as Ratio
(
selcet Region, COUNT(DISTINCT s.KliRC) Celkem
from ..
Union All
selcet Region, COUNT(DISTINCT s.KliRC) Solus
from ..
) A

edited Jul 03 '17 at 15:18

answered Jul 03 '17 at 14:39

Pரதீப்

91,748
19
131
172

score 0 · Answer 3 · answered Jul 03 '17 at 15:14

You can use Common Table Expressions :

    with t1 as
    (
    SELECT
        COUNT(DISTINCT s.KliRC) Celkem
        ,r.Region Region
    FROM dbo.Smlouvy s
            JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
            JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu   
    GROUP BY
        r.Region
    ),
    with t2 as
    (

    SELECT
        COUNT(DISTINCT s.KliRC) Solus
        ,r.Region Region
    FROM dbo.SolusDetaily sd
        JOIN dbo.Smlouvy s      ON sd.CisloZadosti    = s.CisloSmlouvy    
            JOIN CS_OZ oz       ON oz.KodOZ = s.KodOZ
            JOIN CS_Regiony r   ON r.KodRegionu = oz.KodRegionu
    GROUP BY
        r.Region
    ) 
    -- get ratio
    select t1.region , t1.Celkem /t2.Solus as ratio
    from t1
    join t2 on t1. Region = t2.Region

How to make a ratio between values from two queries?

3 Answers3