Attempting SUM() OVER () in PowerShell

Words: 891

Time to read: ~ 5 minutes

Pro-Cras-Tin-Ation!

Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.

Easy Like Sunday Morning

I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.

CREATE TABLE dbo.PushupsOctober
(
	pushup_date date NOT NULL
		CONSTRAINT PK_PushupsOctober PRIMARY KEY CLUSTERED,
	attempt_01 tinyint NULL,
	attempt_02 tinyint NULL,
	attempt_03 tinyint NULL,
	attempt_04 tinyint NULL,
	attempt_05 tinyint NULL,
	attempt_06 tinyint NULL,
	attempt_07 tinyint NULL,
	attempt_08 tinyint NULL,
	total_pushups_per_day AS (ISNULL(attempt_01, 0) + ISNULL(attempt_02, 0) + ISNULL(attempt_03, 0) + ISNULL(attempt_04, 0) + ISNULL(attempt_05, 0) + ISNULL(attempt_06, 0) + ISNULL(attempt_07, 0) + ISNULL(attempt_08, 0))
);
GO

Then, all I have to do is insert the data and SQL will automatically take care of calculating the total per row for me.

INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/07/2020 00:00:00', 20, 20, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/08/2020 00:00:00', 20, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/09/2020 00:00:00', 20, 20, 25, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/10/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/11/2020 00:00:00', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/12/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/13/2020 00:00:00', 20, 15, 15, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/14/2020 00:00:00', 30, 30, 20, 20, 25, 20, 20, 20);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/15/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/16/2020 00:00:00', 25, 25, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/17/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
GO

SELECT	*
FROM	dbo.PushupsOctober;
GO
Why count when not need to?

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

SELECT	*,
		SUM(p.total_pushups_per_day) OVER () AS total_so_far 
FROM	dbo.PushupsOctober AS p;
GO
Total total

Easy Like Monday Morning

PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.

It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.

If you have a better way (you choose the conditions that satisfy “better”) please let me know.

Grabbing the Data

First, let’s grab the data from the table in our database.

$data_2 = Invoke-DbaQuery -SqlInstance localhost -Database LocalTesting -Query @'
SELECT * FROM dbo.PushupsOctober;
'@

Removing Unwanted Properties

Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!

$data_2 = $data_2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, total_pushups_per_day

Grabbing Property Names

There’s a couple of things that we need here. We need a way to add up all the “attempt” columns so we need a way to select them all.

$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name

There, that should do nicely!

Grabbing the Values for those Properties

Now, we can iterate over them and get all the values.

foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }

Potential Problem

See all those empty lines? Yep, that’s a potential problem for Measure-Object.

$hasToBeAnEasierWay = foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
$hasToBeAnEasierWay | Measure-Object -Sum

Removing NULL or WhiteSpace

Thankfully, there’s a way to get rid of those empty lines.

$hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum

Putting it ALL Together

Now that we have the skeleton of a script ready, let’s put it all together.

Row total

$data_2 | ForEach-Object -Begin {
    $props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
} -Process {
    $total = $null
    $hasToBeAnEasierWay = $null

    $hasToBeAnEasierWay = foreach ($prop in $props) {
        $_ | Select-Object -ExpandProperty $prop
    }
    $total = ($hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum).Sum

    $_ | Select-Object -Property *, @{
        Name = 'total_per_day'
        Expression = { $total }
    }, @{
        Name = 'days_left'
        Expression = { ((Get-Date -Date '2020-10-31') - (Get-Date -Date $_.pushup_date)).Days }
    }
} -OutVariable data_3 | Format-Table -Autosize

We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .

Grand Total

Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.

I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.

$data_3 | Select-Object -Property *, @{
    Name = 'total_so_far'
    Expression = { ($data_3 | Measure-Object -Property total_per_day -Sum).Sum }
} | Format-List

There We Go!

While, I’d argue that it’s not as easy as SQL, it’s completely possible to get row totals and grant totals in PowerShell.

Honestly though, I hope there’s an easier way. Otherwise, I’m going to do it in SQL and then grab it out into PowerShell afterwards.

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

2 thoughts on “Attempting SUM() OVER () in PowerShell”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s