Query to find count and sum

%3CLINGO-SUB%20id%3D%22lingo-sub-1652182%22%20slang%3D%22en-US%22%3EQuery%20to%20find%20count%20and%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1652182%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20my%20data-table%20Looks%20like.........%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECategory%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Annual%20savings%3C%2FP%3E%3CP%3E-------%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B------------------%3C%2FP%3E%3CP%3ECategory-1.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FP%3E%3CP%3ECategory-2.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%3C%2FP%3E%3CP%3ECategory-1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%205%3C%2FP%3E%3CP%3ECategory-1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%206%3C%2FP%3E%3CP%3ECategory-3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3ECategory-3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B9%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20Need%20a%20power-shell%20query%20to%20get%20the%20output%20as%20following%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECategory%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Sum(savings)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCount%20(Category)%3C%2FP%3E%3CP%3E-------%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B------------------%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20-----------%3C%2FP%3E%3CP%3ECategory-1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2012%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%3C%2FP%3E%3CP%3ECategory-2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3ECategory-3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B10%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuick%20help%20will%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1652182%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Automation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVisual%20Studio%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1652766%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20find%20count%20and%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1652766%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20that%20your%20trying%20do%20with%20PS1%2C%20as%20per%20query%26nbsp%3B%20seem%20for%20excel%20report.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20better%20descript%20more%20in%20details.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F32724%22%20target%3D%22_blank%22%3E%40praveen%20raju%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1677712%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20to%20find%20count%20and%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1677712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F32724%22%20target%3D%22_blank%22%3E%40praveen%20raju%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20use%20Measure-object%3C%2FP%3E%3CP%3Eit%20will%20be%20like%20this%3C%2FP%3E%3CP%3EYour%20Input%20%7C%26nbsp%3BMeasure-Object%20-Property%20%3CSPAN%3ECategory%26nbsp%3B%3C%2FSPAN%3E-Sum%20%7C%20select%20count%2Csum%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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
  }
}