CODEWITHSUNDEEP
sqlsql-serversql-server-2016
user5021612
user5021612

Reputation:

How to make a ratio between values from two queries?

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.

Upvotes: 1

Views: 1054

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 0

M.Hassan
M.Hassan

Reputation: 11032

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

Upvotes: 0

TriV
TriV

Reputation: 5148

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

Upvotes: 1

Related Questions