CODEWITHSUNDEEP
sqlsql-server
Carlo Luisito
Carlo Luisito

Reputation: 229

Selecting Columns from another table inside a Select Query

It seems to be the query that I have created is a little bit messy. Is there any other way to make it more readable?

Here's the query that I have created.

Select nullif((select count(*) from [casino_game].[dbo].[group_user] gu
        where gu.group_id = (select pt.group_id from [casino_game].[dbo].[promo_trigger] pt
        join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id
        where pt.name = f.promo_name
        group by pt.group_id)),0) as target_group,                           -- Number of customer uploaded to a free game program.

        (cast(count(*) as decimal(11,2)) / nullif((select count(*) from [casino_game].[dbo].[group_user] gu
        where gu.group_id = (select pt.group_id from [casino_game].[dbo].[promo_trigger] pt
        join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id
        where pt.name = f.promo_name
        group by pt.group_id)), 0)) * 100 as claim_rate from [data].[dbo].[testgame]

This query is working, but I need some suggestion if there will be another way to make this working and more readable.

Upvotes: 2

Views: 85

Answers (2)

Eric
Eric

Reputation: 5733

I think the target table was group_user instead of testgame, and it must be a single row, no need for a join to many rows to testgame

select 
    COUNT(*) as target_group,  -- Number of customer uploaded to a free game program.
    -- It may cause the Divide by zero error if count(*) = 0
    CASE WHEN COUNT(*) > 0 THEN (cast((SELECT COUNT(*) as value FROM [data].[dbo].[testgame]) as decimal(11,2)) / COUNT(*)) * 100 END as claim_rate 
from 
    [casino_game].[dbo].[group_user] gu 
    inner join [casino_game].[dbo].[promo_trigger] pt on gu.group_id = pt.group_id
    inner join [casino_game].[dbo].[promo_offer] po on po.trigger_id = pt.trigger_id

Upvotes: 1

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

Reputation: 93694

You can use OUTER APPLY to avoid using the same correlated sub-query twice

SELECT target_group, 
       ( Cast(Count(*) AS DECIMAL(11, 2)) / NULLIF(target_group, 0) ) * 100 AS claim_rate 
FROM   [data].[dbo].[testgame] 
       OUTER apply (SELECT Count(*) AS target_group 
                    FROM   [casino_game].[dbo].[group_user] gu 
                    WHERE  gu.group_id = (SELECT pt.group_id 
                                          FROM 
                           [casino_game].[dbo].[promo_trigger] pt 
                           JOIN [casino_game].[dbo].[promo_offer] 
                                po 
                             ON po.trigger_id = pt.trigger_id 
                                          WHERE  pt.NAME = f.promo_name 
                                          GROUP  BY pt.group_id)) ou

Upvotes: 2

Related Questions