Selecting Columns from another table inside a Select Query

Question

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.

(1) Describe what you are trying to do. (2) Sample data and desired results are helpful. (3) And tag the question with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2016 at 1:44

Pரதீப் · Accepted Answer · 2016-04-06 01:55:29Z

2

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

answered Apr 6, 2016 at 1:55

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eric · Accepted Answer · 2016-04-06 01:57:04Z

1

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

answered Apr 6, 2016 at 1:57

Eric

5,74319 silver badges26 bronze badges

1 Comment

Carlo Luisito Over a year ago

Thanks for the suggestion.

Collectives™ on Stack Overflow

Selecting Columns from another table inside a Select Query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related