Query to find count and sum

Copper Contributor

Here is my data-table Looks like.........

 

Category      Annual savings

-------         ------------------

Category-1.         1

Category-2.         3

Category-1          5

Category-1          6

Category-3          1

Category-3           9

 

I Need a power-shell query to get the output as following

 

Category      Sum(savings)             Count (Category)

-------         ------------------        -----------

Category-1        12                           3

Category-2         3                            1

Category-3         10                           2

 

Quick help will be appreciated.

4 Replies

What is that your trying do with PS1, as per query  seem for excel report. 

It is better descript more in details.@praveen raju 

@praveen raju 

You will need to use Measure-object

it will be like this

Your Input | Measure-Object -Property Category -Sum | select count,sum

@ilyas_2018 

Did the proposed solution helped you in your issue.

 

@praveen raju Slightly delayed (found my way to the tech community via Ignite) but if only for any future searches.

 

You'll want to use Group-Object to collect the values by category (You have an extra period on the first 2 entries which would skew the results).  Assuming you had the data as a csv and imported it to $data you can use the below

 

$data | Group-Object Category | ForEach-Object {
  [pscustomobject]@{
    Category = $_.name
    'Sum(savings)' = ($_.group.'annual savings' | Measure-object -sum).sum
    'Count(Category)' = $_.count
  }
}