Something akin to GROUP BY in SQL

Question

Post reply

Something akin to GROUP BY in SQL

schleep

SSChampion

Points: 12582

July 8, 2019 at 12:33 pm

#3660736

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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246690 More actions · Answer 1

Can you provide sample DDL, INSERT scripts and desired results, please?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

schleep SSChampion Points: 12582 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246690 More actions · Answer 3

I guess I should have checked which forum I was in, before answering 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 4

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

schleep SSChampion Points: 12582 More actions · Answer 5

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.

schleep SSChampion Points: 12582 More actions · Answer 6

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

This reply was modified 4 years, 9 months ago by schleep.