Something akin to GROUP BY in SQL

  • 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!

  • 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.

  • 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

     

     

  • 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.

  • 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

  • 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.

     

  • 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 3 years, 2 months ago by  schleep.

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

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