1

I've got a custom object retrieved from a SQL Query which looks like this

This is basically how I've constructed the object and it works fine but I can't seem to find how to group them.

foreach ($row in $result) {
$props = @{ 'MailboxName' = $row.MailboxName
            'PSTsFound' = $row.PSTsFound
            'Messagesremaining' = $row.Messagesremaining
          }
          $obj = New-Object -TypeName PSObject -Property $props
          $userPSTs += $obj
}

Column1 Column2

User1 3

User1 6

User1 8

User2 10

User2 3

User3 4

User4 6

User4 2

And I want to turn it into this

Column1 Column2

User1 17

User2 13

User3 4

User4 8

So basically sum the column2 where column1 matches.

Thanks in advance for any assistance

1

I assume that you by Column1 and Column2 mean MailboxName and PSTsFound

If you are just interested in the sum of PSTsFound for all rows with the same MailboxName, and not the individual rows, you could use a HashTable with the MailboxName as the key and the entire object as your value. Then in your iteration, test whether your HashTable already contains a given key - if so, add the value to the existing entry:

$SumTable = @{}

foreach ($row in $result) {
    if($SumTable.ContainsKey($row.MailboxName))
    {
        $obj = $SumTable[$row.MailboxName]
        $obj.PSTsFound += $row.PSTsFound
        $obj.Messagesremaining += $row.Messagesremaining
        $SumTable[$row.MailboxName] = $obj
    }
    else
    {
        $props = @{
            'MailboxName' = $row.MailboxName
            'PSTsFound' = $row.PSTsFound
            'Messagesremaining' = $row.Messagesremaining
        }
        $obj = New-Object -TypeName PSObject -Property $props
        $SumTable.Add($obj.MailboxName,$obj)
    }
}

$userPSTs = $SumTable.Values

If you don't want to modify the existing collection, but just want to output the sum, you can utilize the Group-Object and Measure-Object cmdlets in this brilliant approach suggested on StackOverflow:

$userPSTs | Group-Object -Property MailboxName | Foreach-Object {
    New-Object PSObject -Property @{
        MailboxName = $_.MailboxName
        PSTsFound = ($_.Group | Measure-Object -Property PSTsFound -Sum).Sum
        Messagesremaining = ($_.Group | Measure-Object -Property Messagesremaining -Sum).Sum
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.