Adding totals based on titles

Simon_King

hi,

 

I am trying to group together user totals based on their adjacent column title I have tried groups and cases but cant get this to work.  My latest attempt is below, can anyone help.

 

SUM(
CASE
when `PS Variable Stub`="ADenabled" or `PS Variable Stub`="O365enabled" then `GUID_ User Count`
else 0
End
)

 

Thanks

guitarhero23

You seem to have been on the right track, I didn't really need to do anything different than you did.

 

Here are my results based on your sample data

 

 

Total Active Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`

  ELSE 0
  END)
  )

No MFA:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`

  ELSE 0
  END)
  )

Internet Blocked Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`

  ELSE 0
  END)
  )

  -

  SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`

  ELSE 0
  END)
  )

  -

  SUM(
    (CASE 
    WHEN `PS Variable Stub` = 'EnabledWithMFA' THEN `UPN: User Count`
     ELSE 0
    END)
   )

guitarhero23

Can you provide any sample data that I can test with? And the numbers you expect to see?

Simon_King

hi,

 

thanks, basically our of AD i can get a number of AD/O365 I can get totals but need to merge some together into totals i.e.

 

TOTALADaccounts+totalO365accounts=total Active Accounts

 

EnabledInMFAExclude+O365MFANotOnAccountRequired+O365MFAExclude+O365MFANotOnAccountRequired=No MFA

 

total Accounts-NoMFA-EnabledWithMFA=Internet blocked accounts

 

PS Variable Stub	UPN: User Count
TOTALADaccounts	912
EnabledInMFAExclude	2
EnabledWithMFA	281
O365MFANotOnAccountRequired	1
totalO365accounts	39
O365MFAExclude	34
O365MFANotOnAccountRequired	5

 

Numbers and titles have been randomised but you get the jist

 

thanks

guitarhero23

You seem to have been on the right track, I didn't really need to do anything different than you did.

 

Here are my results based on your sample data

 

 

Total Active Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`

  ELSE 0
  END)
  )

No MFA:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`

  ELSE 0
  END)
  )

Internet Blocked Accounts:

SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'TOTALADaccounts' OR `PS Variable Stub` = 'totalO365accounts' THEN `UPN: User Count`

  ELSE 0
  END)
  )

  -

  SUM(
  (CASE 
  WHEN `PS Variable Stub` = 'EnabledInMFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' OR `PS Variable Stub` = 'O365MFAExclude' OR `PS Variable Stub` = 'O365MFANotOnAccountRequired' THEN `UPN: User Count`

  ELSE 0
  END)
  )

  -

  SUM(
    (CASE 
    WHEN `PS Variable Stub` = 'EnabledWithMFA' THEN `UPN: User Count`
     ELSE 0
    END)
   )

Simon_King

Great thanks, Looks like I was mainly missing some brakets!