July 8, 2019 at 12:33 pm
So I have an array:
$array = @()
$Props = [ordered]@{Table="Table1"; Col1=1; Col2=2}
$array += New-Object psobject -Property $Props
$Props = [ordered]@{Table="Table2"; Col1=4; Col2=5}
$array += New-Object psobject -Property $Props
$Props = [ordered]@{Table="Table1"; Col1=3; Col2=7}
$array += New-Object psobject -Property $Props
$Props = [ordered]@{Table="Table2"; Col1=2; Col2=6}
$array += New-Object psobject -Property $Props
I want to get the sum of cols 1 and 2, grouped by table. For the life of me, I can't seem to figure out something which I think should be simple...
Thanks!
July 8, 2019 at 1:58 pm
Can you provide sample DDL, INSERT scripts and desired results, please?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2019 at 2:02 pm
I meant, in Powershell, how can i get the equivalent of
SELECT Table, Col1 = SUM(Col1), Col2 = SUM(Col2)
FROM <$array>
GROUP BY Table
In my example,
Table Col1 Col2
Table1 4 9
Table2 6 11
July 8, 2019 at 2:07 pm
I guess I should have checked which forum I was in, before answering 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 8, 2019 at 3:13 pm
something like this? https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/group-object?view=powershell-6
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
July 8, 2019 at 3:19 pm
I don't *think* Group-Object is what I'm looking for: "...a table with one row for each property value and a column that displays the number of items with that value.". It returns counts of (a) properties.
None of the examples shows any other aggregate than count.
July 11, 2019 at 11:07 am
Found the solution.
And Group-Object does come into it:
# Group the objects by table name
$Tables = $array |Group Table
# Select the table name + sum of each group
$Tables |Select-Object Name,
@{Name='Col1';Expression={($_.Group |Measure-Object Col1 -Sum).Sum}},
@{Name='Col2';Expression={($_.Group |Measure-Object Col2 -Sum).Sum}}
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply