Query to find count and sum

Question

Here is my data-table Looks like.........&nbsp;Category&nbsp; &nbsp; &nbsp; Annual savings-------&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------------------Category-1.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Category-2.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Category-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5Category-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6Category-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Category-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9&nbsp;I Need a power-shell query to get the output as following&nbsp;Category&nbsp; &nbsp; &nbsp; Sum(savings)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Count (Category)-------&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------------------&nbsp; &nbsp; &nbsp; &nbsp; -----------Category-1&nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Category-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Category-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;Quick help will be appreciated.

ilyas_2018 · Answer

What is that your trying do with PS1, as per query&nbsp; seem for excel report.&nbsp;It is better descript more in details.praveen raju&nbsp;

farismalaeb · Answer

praveen raju&nbsp;You will need to use Measure-objectit will be like thisYour Input |&nbsp;Measure-Object -Property Category&nbsp;-Sum | select count,sum

farismalaeb · Answer

ilyas_2018&nbsp;Did the proposed solution helped you in your issue.&nbsp;

jleechpe · Answer

praveen raju&nbsp;Slightly delayed (found my way to the tech community via Ignite) but if only for any future searches.&nbsp;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).&nbsp; Assuming you had the data as a csv and imported it to $data you can use the below&nbsp;$data | Group-Object Category | ForEach-Object {
  [pscustomobject]@{
    Category = $_.name
    'Sum(savings)' = ($_.group.'annual savings' | Measure-object -sum).sum
    'Count(Category)' = $_.count
  }
}&nbsp;

Forum Discussion

Query to find count and sum

Share

Resources