Something akin to GROUP BY in SQL

  • schleep

    SSChampion

    Points: 12529

    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!

  • Phil Parkin

    SSC Guru

    Points: 243485

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • schleep

    SSChampion

    Points: 12529

    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: 243485

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • jonathan.crawford

    SSCertifiable

    Points: 6348

    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: 12529

    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: 12529

    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 1 week, 5 days 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