Adding totals based on titles

Simon_King
Simon_King Member
in Magic ETL

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

Best Answer

  • guitarhero23
    guitarhero23 Contributor
    Answer ✓

    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

    Dojo Help 200.JPG

     

     

    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)
       )

    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

Answers

  • guitarhero23
    guitarhero23 Contributor

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


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Simon_King
    Simon_King Member

    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 StubUPN: User Count
    TOTALADaccounts912
    EnabledInMFAExclude2
    EnabledWithMFA281
    O365MFANotOnAccountRequired1
    totalO365accounts39
    O365MFAExclude34
    O365MFANotOnAccountRequired5

     

    Numbers and titles have been randomised but you get the jist

     

    thanks

  • guitarhero23
    guitarhero23 Contributor
    Answer ✓

    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

    Dojo Help 200.JPG

     

     

    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)
       )

    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Simon_King
    Simon_King Member

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

Categories