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


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 {
    Category = $_.name
    'Sum(savings)' = ($_.group.'annual savings' | Measure-object -sum).sum
    'Count(Category)' = $_.count