SQL GROUP BY with SUM in PowerShell-CodePudding

What is the simplest way to write this in PowerShell:

SELECT col1, SUM(col3) AS SumOfValues
FROM dbo.Table
GROUP BY col1

How can I combine Group-Object and Measure-Object?

I have found this answer link, but there must be a simpler way.

CodePudding user response：

Like this? With a calculated property at the end.

'id,amount
1,4
1,5
2,4
2,6
3,3
3,5' | convertfrom-csv | group id | select name, 
  @{n='Sum';e={$_.group.amount | measure -sum | % sum}}


Name Sum
---- ---
1      9
2     10
3      8

CodePudding user response：

You need to use the property GROUP. The script outputs "$group | Format-Table" which shows what the real issue is.

This is what each row of the Group Looks like :

Count Name Group
----- ---- -----
    3 a    {@{col1=a; col2=x; col3=1}, @{col1=a; col2=x; col3=2}, @{col1=a; col2=x; col3=3}}

See code below :

$input = @"
col1,col2,col3
a,x,1
a,x,2
a,x,3
b,x,4
b,x,5
b,x,6
c,x,7
c,x,8
c,x,9
"@

$table = $input | ConvertFrom-Csv
$table | Format-Table

$groups = $table | Group-Object {$_.col1}

$outputTable = [System.Collections.ArrayList]::new()
foreach($group in $groups)
{
$group | Format-Table
   $newRow = New-Object -TypeName psobject
   $newRow | Add-Member -NotePropertyName col1 -NotePropertyValue $group.Name
   $sum = 0
   $group.Group | foreach{$sum  = $_.col3}
Write-Host "sum = " $sum
   $newRow | Add-Member -NotePropertyName SumOfValues -NotePropertyValue $sum
   $outputTable.Add($newRow)  | Out-Null
}
$outputTable | Format-Table