Sep 10 2020 02:13 AM
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.
Sep 10 2020 05:48 AM
What is that your trying do with PS1, as per query seem for excel report.
It is better descript more in details.@praveen raju
Sep 17 2020 04:24 AM
You will need to use Measure-object
it will be like this
Your Input | Measure-Object -Property Category -Sum | select count,sum
Sep 23 2020 02:22 AM
Sep 23 2020 05:21 PM
@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
}
}