CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Voice Of The Rain
Voice Of The Rain

Reputation: 587

Select totals to return only one record in SQL Server

After spending more than 3 hours on this I gave up.

I have four tables: 

Users, Approvals, Centers, Managements

My ultimate goal is to get the total number of users in each management separated by the user role (I have two roles here : Parents and Society members)

I've been using the following code 

select 
    (select count(r.StudentId) 
     from Users u 
     where u.UserId = r.StudentId and u.RoleId = 10) as Parents,
    (select count(r.StudentId) 
     from Users u  
     where u.UserId = r.StudentId and u.RoleId = 11) as SocietyMembers,
    e.ManagementId, e.ManagmentTitle
from 
    CentersRegistrationsApprovals r --ON r.StudentId = u.UserId
inner join
    Centers c ON c.CenterId = r.CenterId
inner join
    Managments e ON e.ManagementId = c.EducationManagementId
group by 
    e.ManagementId, e.ManagmentTitle, StudentId

I'm expecting the query result to be as the following :

Parents   SocietyMambers   ManagementId   ManagementName
----------------------------------------------------------------
3         3                10             North Region

However the result set always gives me 

Parents   SocietyMambers   ManagementId   ManagementName
----------------------------------------------------------------
3         NULL             10             North Region
NULL      3                10             North Region

Any ideas how to consolidate the result to only 1 record?

Upvotes: 1

Views: 76

Answers (2)

Adam Tokarski
Adam Tokarski

Reputation: 697

Please try something like this (not tested)

; with CTE1 as (
    select 
        (select count(r.StudentId) 
         from Users u 
         where u.UserId = r.StudentId and u.RoleId = 10) as Parents,
        (select count(r.StudentId) 
         from Users u  
         where u.UserId = r.StudentId and u.RoleId = 11) as SocietyMembers,
        e.ManagementId, e.ManagmentTitle
    from 
        CentersRegistrationsApprovals r --ON r.StudentId = u.UserId
    inner join
        Centers c ON c.CenterId = r.CenterId
    inner join
        Managments e ON e.ManagementId = c.EducationManagementId
    group by 
        e.ManagementId, e.ManagmentTitle, StudentId
)
SELECT MAX(Parents), MAX(SocietyMembers), ManagementId, StudentId
FROM CTE1
GROUP BY ManagementId, StudentId

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can query like below:

select 
     Sum(case when u.roleId = 10 then 1 else 0 end) as Parents,
     Sum(case when u.roleId = 11 then 1 else 0 end) as SocietyMembers,
     e.ManagementId, e.ManagmentTitle
from 
    CentersRegistrationsApprovals r --ON r.StudentId = u.UserId
inner join
    Centers c ON c.CenterId = r.CenterId
inner join
    Managments e ON e.ManagementId = c.EducationManagementId
Join Users u ON r.StudentId = u.UserId
group by 
    e.ManagementId, e.ManagmentTitle

Upvotes: 2

Related Questions