CODEWITHSUNDEEP
sqlsql-server
user3442107
user3442107

Reputation: 65

sql server ratio across 2 result sets with group by

Should hopefully be a simple question, but I can't quite get my head around it. Sql server 2012

Basically, I have a users table. Each user has a 'pincode', which is the foreign key to the organisation they below to. Each user then completes a number of modules, stored in a separate table linked by their user_id. What I want to get is a list of all the pincodes, with the total number of users, then the total number of completions, and then a ratio of number of users divided by number of completions.

The following code gives me a list of results, with the first result being the number of completions, and the second result the number of users, so this is the data I need, I just can't figure out how to return as one result with a ratio column. So...

select pincode,   count(*) as totcomps from modules 
inner join users on users.user_id=modules.user_id  
group  by pincode

union all

select pincode,   count(*) as totusers from users  
group  by pincode
order by pincode

returns me 

site1    50 // number of completions
site1    10 // number of users
site2    130
site2   12
site3    2
site3    1 
etc 
.

What I want is it to return as:

site1 50, 10, 5   // pincode, totcomps, totusers, totcomps/totusers
site2 130, 12, 10.83
site3 2, 1, 2
etc....

As always, your help is much appreciated

Upvotes: 0

Views: 36

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Here is one way you could tackle this. Not 100% certain this works because we didn't have any ddl or sample data.

with pincodes as
(
    select pincode
        , count(*) as totcomps 
    from modules 
    inner join users on users.user_id = modules.user_id  
    group by pincode
)
, Users as
(
    select pincode
        , count(*) as totusers 
    from users  
    group by pincode
)

select p.pincode
    , p.totcomps
    , u.totusers
    , p.totcomps / (u.totusers * 1.0) --need to multiply by 1.0 to avoid integer division
from pincodes p
join Users u on u.pincode = p.pincode

Upvotes: 1

Related Questions