DataTable group and sum data

by

I have a Powershell DataTable with two columns, pobox [string] and pages [int].

Data:

pobox   pages   
7002        5
7002        2
7002       10
7004        1
7004        8
7006        2
7006        1

I am trying to group and sum the data like this

pobox   pages   
7002       17
7004        9
7006        3

I have tried countless combos with Group-Object, Select-Object.

    $dataTable | Group-Object ...
    $dataTable | Group-Object | Select-Object ...

But it seems completely impossible for me to find any solution that works.

In SQL it is very easy:

SELECT
  pobox,
  SUM(pages) AS pages
FROM pagecount
GROUP BY pobox;

A solution with a hash table for storing sums.

$groupby = @{}
$dataTable | ForEach-Object { $groupby[$_.pobox] += [int]$_.pages }
$groupby | Format-Table -AutoSize

Result:

Name Value
---- -----
7006 3
7004 9
7002 17

Try the following.


$dat_grouped = $dat_table | Group-Object pobox | ForEach-Object 
{[PSCustomObject]@{pobox = $_.Name; pages = ($_.Group | Measure-Object pages -Sum).Sum}}

$dat_grouped | Format-Table -AutoSize

Leave a Comment