SUM() with a COUNT() statement

Question

I'm wondering if it is possible to sum specific values after a count statement. At the moment I have:

SELECT roles.Name AS Role_Name , COUNT(DISTINCT ur.UserId ) AS RoleCount FROM dbo.AbpUsers as users 
LEFT JOIN resourcing.CompanyorganizationUnitUserRole AS ur on users.Id = ur.UserId
LEFT JOIN dbo.AbpRoles AS roles ON roles.Id = ur.RoleId
GROUP BY roles.Name;

Which outputs:

Role_Name:                    |RoleCount:
TimeTrackingWritingMember     |8277
OrganizationUnitLeader        |529
Member                        |13901
DepartmentalCoordinator       |759
OrganizationUnitSecondLeader  |4
ResourceCoordinator           |6

The idea is to SUM for example the roleCount of TimeTrackingWritingMember + Member = "Basic User"

I don't seem to get the logic how i could use both in one query. All help would be much appreciated

you have to provide sample data & desired output – eshirvana Nov 16 '21 at 14:59 — eshirvana, Nov 16 '21 at 14:59

score 1 · Answer 1 · answered Nov 16 '21 at 16:03

1

You can Select the sum of those two Records.

  SELECT SUM(A.ROLECOUNT) as 'Basic User'
    FROM(
        SELECT roles.Name AS Role_Name,
        COUNT(DISTINCT ur.UserId ) AS RoleCount 
        FROM dbo.AbpUsers as users 
        LEFT JOIN resourcing.CompanyorganizationUnitUserRole AS ur 
        on users.Id = ur.UserId
        LEFT JOIN dbo.AbpRoles AS roles ON roles.Id = ur.RoleId
        GROUP BY roles.Name;
        ) A 
    WHERE A.ROLE_NAME IN ('TimeTrackingWritingMember', 'Member')

answered Nov 16 '21 at 16:03

Zyan

69
4

Hey , thanks. So where it says "A" this means the table it needs to get it from ? Thanks in advance – Samuel Van Bladel Nov 17 '21 at 08:45
It keeps giving me an error saying that there is a syntax error near "Name" but it seems fine – Samuel Van Bladel Nov 17 '21 at 08:52
Sorry , it was the semicolon ";" after name in the nested query. – Samuel Van Bladel Nov 17 '21 at 09:14
Now I tried to fit another one into it but it gives me the issue that when nesting it the SELECT SUM(A.ROLECOUNT) as 'Basic User' , (SELECT SUM(A.ROLECOUNT) as 'Advanced User') with another WHERE clause shows not the column name as "advanced user" but just COLUMN1 ... as if it ignores only the "AS" statement, any ideas and already massive thanks – Samuel Van Bladel Nov 17 '21 at 09:34

SUM() with a COUNT() statement

1 Answers1