{"id":2191,"date":"2012-10-03T17:12:00","date_gmt":"2012-10-03T17:12:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/powershell\/2012\/10\/03\/joining-multiple-tables-grouping-and-evaluating-totals\/"},"modified":"2019-02-18T13:05:35","modified_gmt":"2019-02-18T20:05:35","slug":"joining-multiple-tables-grouping-and-evaluating-totals","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell\/joining-multiple-tables-grouping-and-evaluating-totals\/","title":{"rendered":"Joining Multiple Tables, Grouping and Evaluating Totals"},"content":{"rendered":"
\n

Joining Multiple Tables, Grouping And Evaluating Totals<\/b><\/p>\n

This is a continuation to the Join-Object<\/a> blog post considering multiple joins and SQL style grouping and total evaluation.<\/p>\n

Problem:<\/b><\/p>\n

In the Join-Object<\/a> blog we’ve seen that relational data naturally organizes in multiple tables. Once we have those tables, we frequently want to see the information back together, so we need something like Join-Object<\/a>. Sometimes we also need to group the information and evaluate totals inside the groups. For instance, what are the total sales for each customer?<\/p>\n

How can we use Join-Object<\/a> <\/span>in multiple tables and group information from the tables to report totals?<\/p>\n

Joining multiple tables:<\/b><\/p>\n

Consider the following tables describing sales at a fruit stand:<\/p>\n

\n\n\n\n\n\n\n\n
\n

Customers<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Id<\/p>\n<\/td>\n

\n

Name<\/p>\n<\/td>\n

\n

Phone<\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

John Smith<\/p>\n<\/td>\n

\n

(206)555-8899<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

Mark Stone<\/p>\n<\/td>\n

\n

(425)999-8811<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

Ruth Moore<\/p>\n<\/td>\n

\n

(425)888-3355<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

\n\n\n\n\n\n\n\n
\n

Products<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Id<\/p>\n<\/td>\n

\n

Name<\/p>\n<\/td>\n

\n

Price<\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

Apple<\/p>\n<\/td>\n

\n

10<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

Orange<\/p>\n<\/td>\n

\n

12<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

Kiwi<\/p>\n<\/td>\n

\n

15<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/span><\/p>\n\n\n\n\n\n\n\n
\n

Orders<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Id<\/p>\n<\/td>\n

\n

Date<\/p>\n<\/td>\n

\n

CustomerId<\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

6\/6\/2012 9:12:44 AM<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

6\/7\/2012 6:12:10 PM<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

6\/5\/2012 12:05:03 PM<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n
\n

OrderItems<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Id<\/p>\n<\/td>\n

\n

Quantity<\/p>\n<\/td>\n

\n

ProductId<\/p>\n<\/td>\n

\n

OrderId<\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

3<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n<\/tr>\n

\n

3<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n<\/tr>\n

\n

4<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n<\/tr>\n

\n

5<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n<\/tr>\n

\n

6<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

As we saw in the Join-Object<\/a> blog, we need this many tables to deal with the simple conceptual problem of selling fruit!<\/p>\n

One might question the need for the OrderItems table. An order item is a line in the receipt for an order that lists a product and its quantity. Since there can be many items per order, it needs its own table as mentioned in the Join-Object<\/a> blog.<\/p>\n

As we can see from the data, we have 3 customers, 3 products, 3 orders, and 6 order items. The three first order items are for the Order with Id 1.<\/p>\n

Good data design forces us to split the tables, but after this is done, we frequently need to see the data together.<\/p>\n

For example, suppose I want a report with all basic receipt information, including customer name, order date, all products bought, their quantity, and price. Here is the SQL statement that produces the report:<\/p>\n

SELECT     Customer.Name, [Order].Date, OrderItem.Quantity,<\/span><\/p>\n

           Product.Name AS [Product Name], Product.Price<\/span><\/p>\n

FROM       OrderItem INNER JOIN Product ON OrderItem.ProductId = Product.id INNER JOIN<\/span><\/p>\n

           Customer INNER JOIN  [Order] ON Customer.id = [Order].CustomerId ON OrderItem.OrderId = [Order].id<\/span><\/p>\n

 <\/p>\n

Here is the result:<\/p>\n

 <\/p>\n

\n\n\n\n\n\n\n\n\n\n\n
\n

Joining All Tables<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

Name<\/p>\n<\/td>\n

\n

Date<\/p>\n<\/td>\n

\n

Quantity<\/p>\n<\/td>\n

\n

Product Name<\/p>\n<\/td>\n

\n

Price<\/p>\n<\/td>\n<\/tr>\n

\n

John Smith<\/p>\n<\/td>\n

\n

6\/6\/2012 9:12:44 AM<\/p>\n<\/td>\n

\n

2<\/p>\n<\/td>\n

\n

Apple<\/p>\n<\/td>\n

\n

10<\/p>\n<\/td>\n<\/tr>\n

\n

John Smith<\/p>\n<\/td>\n

\n

6\/6\/2012 9:12:44 AM<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

Orange<\/p>\n<\/td>\n

\n

12<\/p>\n<\/td>\n<\/tr>\n

\n

John Smith<\/p>\n<\/td>\n

\n

6\/6\/2012 9:12:44 AM<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

Kiwi<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n<\/tr>\n

\n

John Smith<\/p>\n<\/td>\n

\n

6\/7\/2012 6:12:10 PM<\/p>\n<\/td>\n

\n

5<\/p>\n<\/td>\n

\n

Apple<\/p>\n<\/td>\n

\n

10<\/p>\n<\/td>\n<\/tr>\n

\n

John Smith<\/p>\n<\/td>\n

\n

6\/7\/2012 6:12:10 PM<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

Orange<\/p>\n<\/td>\n

\n

12<\/p>\n<\/td>\n<\/tr>\n

\n

Mark Stone<\/p>\n<\/td>\n

\n

6\/5\/2012 12:00:00 AM<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

Apple<\/p>\n<\/td>\n

\n

10<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

A little explanation of the SQL statement<\/b><\/p>\n

The  “AS [Product Name]” segment sets the column name in the output. Both product name and customer name are called “Name” in their own tables, so we need to differentiate them in the output.<\/p>\n

The brackets around “[Order]” differentiate it from the SQL “Order” keyword.<\/p>\n

The Inner Join shows only the items present in both tables.<\/p>\n

All 4 tables are “inner joined” in the statement.<\/p>\n

 <\/b><\/p>\n

Putting the data in Windows PowerShell<\/b><\/p>\n

From here on, we will use Windows PowerShell to manipulate this data, so let’s start by putting this data in PowerShell.<\/p>\n

The following script creates four CSV files and then imports them into four variables that contain our data.<\/p>\n

@”<\/span><\/p>\n

Id,Name,Phone<\/span><\/p>\n

1,John Smith,(206)555-8899<\/span><\/p>\n

2,Mark Stone,(425)999-8811<\/span><\/p>\n

3,Ruth Moore,(425)888-3355<\/span><\/p>\n

“@<\/span> ><\/span> c:\\temp\\customers.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

$customers<\/span>=<\/span>import-csv<\/span> c:\\temp\\customers.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

@”<\/span><\/p>\n

Id,Name,Price<\/span><\/p>\n

1,Apple,10<\/span><\/p>\n

2,Orange,12<\/span><\/p>\n

3,Kiwi,15<\/span><\/p>\n

“@<\/span> ><\/span> c:\\temp\\products.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

$products<\/span>=<\/span>import-csv<\/span> c:\\temp\\products.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

@”<\/span><\/p>\n

Id,Date,CustomerId<\/span><\/p>\n

1,6\/6\/2012 9:12:44 AM,1<\/span><\/p>\n

2,6\/7\/2012 6:12:10 PM,1<\/span><\/p>\n

3,6\/5\/2012 12:05:03 PM,2<\/span><\/p>\n

“@<\/span> ><\/span> c:\\temp\\orders.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

$orders<\/span>=<\/span>import-csv<\/span> c:\\temp\\orders.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

@”<\/span><\/p>\n

Id,Quantity,ProductId,OrderId<\/span><\/p>\n

1,2,1,1<\/span><\/p>\n

2,1,2,1<\/span><\/p>\n

3,4,3,1<\/span><\/p>\n

4,5,1,2<\/span><\/p>\n

5,4,2,2<\/span><\/p>\n

6,1,1,3<\/span><\/p>\n

“@<\/span> ><\/span> c:\\temp\\orderItems.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

$orderItems<\/span>=<\/span>import-csv<\/span> c:\\temp\\orderItems.csv<\/span><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

“Customers “<\/span>;$customers<\/span> |<\/span> format-table<\/span> -AutoSize<\/span><\/span><\/p>\n

“`r`nProducts “<\/span>;$products<\/span> |<\/span> format-table<\/span> -AutoSize<\/span><\/span><\/p>\n

“`r`nOrders “<\/span>;$orders<\/span> |<\/span> format-table<\/span> -AutoSize<\/span><\/span><\/p>\n

“`r`nOrder Items “<\/span>;$orderItems<\/span> |<\/span> format-table<\/span> -AutoSize <\/span><\/span><\/p>\n

 <\/p>\n

Here is the output:<\/p>\n

Customers <\/span><\/p>\n

 <\/span><\/p>\n

Id Name        Phone <\/span><\/p>\n

— —-        —– <\/span><\/p>\n

1  John Smith  (206)555-8899<\/span><\/p>\n

2  Mark Stone  (425)999-8811<\/span><\/p>\n

3  Ruth Moore  (425)888-3355<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

Products <\/span><\/p>\n

 <\/span><\/p>\n

Id Name    Price<\/span><\/p>\n

— —-    —–<\/span><\/p>\n

1  Apple   10 <\/span><\/p>\n

2  Orange  12 <\/span><\/p>\n

3  Kiwi    15 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

Orders <\/span><\/p>\n

 <\/span><\/p>\n

Id Date      CustomerId<\/span><\/p>\n

— —-      ———-<\/span><\/p>\n

1  6\/6\/2012  9:12:44 AM 1 <\/span><\/p>\n

2  6\/7\/2012  6:12:10 PM 1 <\/span><\/p>\n

3  6\/5\/2012  12:05:03 PM 2 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

Order Items <\/span><\/p>\n

 <\/span><\/p>\n

Id Quantity ProductId OrderId<\/span><\/p>\n

— ——– ——— ——-<\/span><\/p>\n

1  2        1         1 <\/span><\/p>\n

2  1        2         1 <\/span><\/p>\n

3  4        3         1 <\/span><\/p>\n

4  5        1         2 <\/span><\/p>\n

5  4        2         2 <\/span><\/p>\n

6  1        1         3 <\/span><\/p>\n

 <\/span><\/p>\n

 <\/p>\n

The equivalent joins in PowerShell with the help of Join-Object<\/a> are as follows:<\/p>\n

 <\/span><\/p>\n

# Joins the customer to his orders and places Name(from Customer),<\/span><\/p>\n

# Date(from Order) and Id (from Order)<\/span><\/p>\n

$customerOrders<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customers<\/span> -Right<\/span> $orders<\/span> -LeftProperties<\/span> Name<\/span> -RightProperties<\/span> Date<\/span>,<\/span>Id<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>Id -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>CustomerId}<\/span><\/p>\n

 <\/span><\/p>\n

# Adds the OrderItems to the data already gathered. The output objects will<\/span><\/p>\n

# have Name(from Customer), Date(from Order), Quantity(from OrderItem) <\/span><\/p>\n

# and ProductId (from OrderItem).<\/span><\/p>\n

$customerOrderItems<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customerOrders<\/span> -Right<\/span> $orderItems<\/span> -LeftProperties<\/span> Name<\/span>,<\/span>Date<\/span> -RightProperties<\/span> Quantity<\/span>,<\/span>ProductId<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>Id -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>OrderId}<\/span><\/p>\n

 <\/span><\/p>\n

# Adds the Products to the data already gathered. The output objects will<\/span><\/p>\n

# have Name (from Customer), Date(from Order), Quantity (from OrderItem)<\/span><\/p>\n

# Product Name (from Product) and Price from Product<\/span><\/p>\n

# We use a Hashtable instead of simply “Name” for Product Name because<\/span><\/p>\n

# “Name” is already present in the output object for the customer name,<\/span><\/p>\n

# so we need to differentiate the Product name from that.<\/span><\/p>\n

# If you did not know the syntax for the Hashtable Expression, you will<\/span><\/p>\n

# be interested in the fact it also works for Select-Object properties<\/span><\/p>\n

$customerProducts<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customerOrderItems<\/span> -Right<\/span> $products<\/span> -LeftProperties<\/span> Name<\/span>,<\/span>Date<\/span>,<\/span>Quantity<\/span> -RightProperties<\/span> @{Name=<\/span>“Product Name”<\/span>;Expression=<\/span>{$_<\/span>.<\/span>Name}},<\/span>Price<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>ProductId -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>Id}<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

$customerProducts<\/span> |<\/span> format-table <\/span><\/span><\/p>\n

 <\/span><\/p>\n

Here is the output:<\/p>\n

Name       Date                 Quantity Product Name Price<\/span><\/p>\n

—-       —-                 ——– ———— —–<\/span><\/p>\n

John Smith 6\/6\/2012 9:12:44 AM  2        Apple        10   <\/span><\/p>\n

John Smith 6\/6\/2012 9:12:44 AM  1        Orange       12   <\/span><\/p>\n

John Smith 6\/6\/2012 9:12:44 AM  4        Kiwi         15   <\/span><\/p>\n

John Smith 6\/7\/2012 6:12:10 PM  5        Apple        10   <\/span><\/p>\n

John Smith 6\/7\/2012 6:12:10 PM  4        Orange       12   <\/span><\/p>\n

Mark Stone 6\/5\/2012 12:05:03 PM 1        Apple        10   <\/span><\/p>\n

 <\/span><\/p>\n

 <\/p>\n

So, the following three lines of code …<\/p>\n

$customerOrders<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customers<\/span> -Right<\/span> $orders<\/span> -LeftProperties<\/span> Name<\/span> -RightProperties<\/span> Date<\/span>,<\/span>Id<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>Id -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>CustomerId}<\/span><\/p>\n

 <\/span><\/p>\n

$customerOrderItems<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customerOrders<\/span> -Right<\/span> $orderItems<\/span> -LeftProperties<\/span> Name<\/span>,<\/span>Date<\/span> -RightProperties<\/span> Quantity<\/span>,<\/span>ProductId<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>Id -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>OrderId}<\/span><\/p>\n

 <\/span><\/p>\n

$customerProducts<\/span>=<\/span>Join-Object<\/span> -Left<\/span> $customerOrderItems<\/span> -Right<\/span> $products<\/span> -LeftProperties<\/span> Name<\/span>,<\/span>Date<\/span>,<\/span>Quantity<\/span> -RightProperties<\/span> @{Name=<\/span>“Product Name”<\/span>;Expression=<\/span>{$_<\/span>.<\/span>Name}},<\/span>Price<\/span> -Where<\/span> {$args<\/span>[<\/span>0<\/span>].<\/span>ProductId -eq<\/span> $args<\/span>[<\/span>1<\/span>].<\/span>Id}<\/span><\/p>\n

 <\/span><\/p>\n

… do the same job as the SQL statement:<\/p>\n

 <\/p>\n

SELECT   Customer.Name, [Order].Date, OrderItem.Quantity,<\/span><\/p>\n

         Product.Name AS [Product Name], Product.Price<\/span><\/p>\n

FROM     OrderItem INNER JOIN Product ON OrderItem.ProductId = Product.id INNER JOIN<\/span><\/p>\n

         Customer INNER JOIN  [Order] ON Customer.id = [Order].CustomerId ON OrderItem.OrderId = [Order].id<\/span><\/p>\n

 <\/p>\n

There are some advantages to the SQL statement:<\/p>\n

·         <\/span><\/span>It is a single statement that is somewhat shorter than the three statements  in PowerShell.<\/p>\n

·         <\/span><\/span>It has a better syntax for aliasing a property in the output (“as ProductName” as opposed to “@{Name=<\/span>“Product Name”<\/span>;Expression=<\/span>{$_<\/span>.<\/span>Name}}<\/span>”).<\/p>\n

 <\/p>\n

In my opinion, those are small advantages. Each one of the PowerShell join statements is easy to understand in isolation. The hashtable syntax used in the third command (to change a property name) is a bit long, but very common to PowerShell users, because  it is useful for other cmdlets including Select-Object.<\/p>\n

I considered changing Join-Object to take all the lists and Where clauses in a single cmdlet call, but the need to pair each of two lists with a Where clause (and a Join type) makes the command a bit too cryptic for my taste. I prefer three statements that are easy to understand to one that is not. Performance-wise, there is no difference, because each Join has to be considered in isolation.<\/p>\n

Now that the data is joined, what about getting the total money spent by each customer, the total money spent in each product and the total money spent?<\/p>\n

Total Money Spent by Each Customer<\/b><\/p>\n

Let’s start with the whole command:<\/p>\n

 <\/span><\/p>\n

$customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span> |<\/span> `<\/span><\/p>\n

    Select-Object<\/span> Name<\/span>,<\/span>@{Name=<\/span>‘Total’<\/span>;<\/span><\/p>\n

                         Expression=<\/span>{$_<\/span>.<\/span>Group |<\/span> ForEach-Object<\/span> `<\/span><\/p>\n

                           -Begin<\/span> {$total<\/span>=<\/span>0<\/span>;} `<\/span><\/p>\n

                           -Process<\/span> {$total<\/span>+=[<\/span>int<\/span>]<\/span>$_<\/span>.<\/span>Price*<\/span>$_<\/span>.<\/span>Quantity} `<\/span><\/p>\n

                           -End<\/span> {$total<\/span>}}} |<\/span> format-table<\/span>   <\/span><\/p>\n

 <\/p>\n

 <\/p>\n

It produces this output:<\/p>\n

Name       Sum<\/span><\/p>\n

—-       —<\/span><\/p>\n

John Smith 190<\/span><\/p>\n

Mark Stone  10<\/span><\/p>\n

 <\/span><\/p>\n

 <\/p>\n

The first line:<\/p>\n

$customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span><\/span><\/p>\n

Produces two groups:<\/p>\n

Count Name                      Group                         <\/span><\/p>\n

—– —-                      —–                                                                                         <\/span><\/p>\n

    5 John Smith                {@{Name=John Smith; Date=6\/6\/2012 9:12:44 AM;…<\/span><\/p>\n

    1 Mark Stone                {@{Name=Mark Stone; Date=6\/5\/2012 12:05:03 PM;…   <\/span><\/p>\n

 <\/p>\n

In order to better see what is in each group, we run:<\/p>\n

($customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span>)[<\/span>0<\/span>].<\/span>Group <\/span><\/p>\n

 <\/span><\/p>\n

Which produces:<\/p>\n

Name        : John Smith<\/span><\/p>\n

Date        : 6\/6\/2012 9:12:44 AM<\/span><\/p>\n

Quantity    : 2<\/span><\/p>\n

ProductName : Apple<\/span><\/p>\n

Price       : 10<\/span><\/p>\n

 <\/span><\/p>\n

Name        : John Smith<\/span><\/p>\n

Date        : 6\/6\/2012 9:12:44 AM<\/span><\/p>\n

Quantity    : 1<\/span><\/p>\n

ProductName : Orange<\/span><\/p>\n

Price       : 12<\/span><\/p>\n

 <\/span><\/p>\n

Name        : John Smith<\/span><\/p>\n

Date        : 6\/6\/2012 9:12:44 AM<\/span><\/p>\n

Quantity    : 4<\/span><\/p>\n

ProductName : Kiwi<\/span><\/p>\n

Price       : 15<\/span><\/p>\n

 <\/span><\/p>\n

Name        : John Smith<\/span><\/p>\n

Date        : 6\/7\/2012 6:12:10 PM<\/span><\/p>\n

Quantity    : 5<\/span><\/p>\n

ProductName : Apple<\/span><\/p>\n

Price       : 10<\/span><\/p>\n

 <\/span><\/p>\n

Name        : John Smith<\/span><\/p>\n

Date        : 6\/7\/2012 6:12:10 PM<\/span><\/p>\n

Quantity    : 4<\/span><\/p>\n

ProductName : Orange<\/span><\/p>\n

Price       : 12 <\/span><\/p>\n

 <\/p>\n

And:<\/p>\n

($customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span>)[<\/span>1<\/span>].<\/span>Group <\/span><\/p>\n

 <\/span><\/p>\n

Which produces:<\/p>\n

Name        : Mark Stone<\/span><\/p>\n

Date        : 6\/5\/2012 12:05:03 PM<\/span><\/p>\n

Quantity    : 1<\/span><\/p>\n

ProductName : Apple<\/span><\/p>\n

Price       : 10 <\/span><\/p>\n

 <\/p>\n

So, inside the “Group” property of each object in the output of group-object is the Price and Quantity we want to multiply in order to get a total. If we wanted just the total quantity of fruit we could do:<\/p>\n

$customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span> |<\/span> `<\/span><\/p>\n

    Select-Object<\/span> Name<\/span>,<\/span> `<\/span><\/p>\n

    @{Name=<\/span>‘Total’<\/span>;Expression=<\/span>{($_<\/span>.<\/span>Group |<\/span> Measure-Object<\/span> Quantity<\/span> -Sum<\/span>).<\/span>Sum}} `<\/span><\/p>\n

    |<\/span> format-table<\/span>  <\/span><\/p>\n

 <\/span><\/p>\n

With output:<\/p>\n

Name       Total<\/span><\/p>\n

—-       —–<\/span><\/p>\n

John Smith    16<\/span><\/p>\n

Mark Stone     1 <\/span><\/p>\n

 <\/p>\n

Name<\/span> is the first column in the output. The second column is given by:<\/p>\n

@{Name=<\/span>‘Total’<\/span>; Expression=<\/span>{($_<\/span>.<\/span>Group |<\/span> Measure-Object<\/span> Quantity<\/span> -Sum<\/span>).<\/span>Sum}}<\/span><\/p>\n

 <\/p>\n

This hashtable is a syntax for select object which means a column with a Name = Total and a value which is the Expression. The Expression gets the value of the Group property and pipes to Measure-Object to sum the ‘Quantity’ property of each member of the Group. Finally the Sum property of the Measure-Object result is retrieved. That property contains the actual sum.<\/p>\n

Since we want to get Price * Quantity and measure-object only supports plain properties we end up with the full command repeated here:<\/p>\n

$customerProducts<\/span> |<\/span> Group-Object<\/span> name<\/span> |<\/span> `<\/span><\/p>\n

    Select-Object<\/span> Name<\/span>,<\/span>@{Name=<\/span>‘Total’<\/span>;<\/span><\/p>\n

                         Expression=<\/span>{$_<\/span>.<\/span>Group |<\/span> ForEach-Object<\/span> `<\/span><\/p>\n

                           -Begin<\/span> {$total<\/span>=<\/span>0<\/span>;} `<\/span><\/p>\n

                           -Process<\/span> {$total<\/span>+=[<\/span>int<\/span>]<\/span>$_<\/span>.<\/span>Price*<\/span>$_<\/span>.<\/span>Quantity} `<\/span><\/p>\n

                           -End<\/span> {$total<\/span>}}} |<\/span> format-table<\/span>   <\/span><\/p>\n

 <\/p>\n

Instead of | measure-object we have a | ForEach-Object where we use –Begin, -Process and –End to evaluate the total. This could be easily modified to calculate anything else, including averages, standard deviations, etc.<\/p>\n

 <\/span><\/p>\n

Note: The [int] cast in front of $_.Price is required because the price is a string out of our CSV file. This could be avoided by using Export-CliXml and Import-CliXml for our data, whioch would preserve the data type.<\/p>\n

 <\/p>\n

Here is the corresponding SQL statement:<\/p>\n

SELECT   Customer.Name, SUM(OrderItem.Quantity * Product.Price)<\/b> AS SUM<\/span><\/p>\n

FROM     OrderItem INNER JOIN<\/span><\/p>\n

            Product ON OrderItem.ProductId = Product.id INNER JOIN<\/span><\/p>\n

            Customer INNER JOIN<\/span><\/p>\n

            [Order] ON Customer.id = [Order].CustomerId ON OrderItem.OrderId = [Order].id<\/span><\/p>\n

GROUP BY Customer.Name<\/b><\/span><\/p>\n

 <\/b><\/p>\n

The SQL statement combines the joining, grouping and total evaluation(Price * Quantity) operations.<\/p>\n

The Powershell statement is only doing the grouping and total evaluation, since the $customerProducts variable has the result of the joins performed in the previous section of this blog post.<\/p>\n

The bold segments of the SQL statement are performing the grouping and the PowerShell statement.<\/p>\n

SQL is designed specifically for this kind of data, so it provides a shorter syntax overall, but I believe the PowerShell equivalent is simple enough to understand.<\/p>\n

Total Money Spent in Each Product<\/b><\/p>\n

The only thing that changes from the previous section is that the property we’re passing to Group-Object is “ProductName”:<\/p>\n

$customerProducts<\/span> |<\/span> Group-Object<\/span> ProductName<\/span> |<\/span> `<\/span><\/p>\n

    Select-Object<\/span> Name<\/span>,<\/span>@{Name=<\/span>‘Total’<\/span>;<\/span><\/p>\n

                         Expression=<\/span>{$_<\/span>.<\/span>Group |<\/span> ForEach-Object<\/span> `<\/span><\/p>\n

                           -Begin<\/span> {$total<\/span>=<\/span>0<\/span>;} `<\/span><\/p>\n

                           -Process<\/span> {$total<\/span>+=[<\/span>int<\/span>]<\/span>$_<\/span>.<\/span>Price*<\/span>$_<\/span>.<\/span>Quantity} `<\/span><\/p>\n

                           -End<\/span> {$total<\/span>}}} |<\/span> format-table<\/span>   <\/span><\/p>\n

 <\/p>\n

Name   Sum<\/span><\/p>\n

—-   —<\/span><\/p>\n

Apple   80<\/span><\/p>\n

Orange  60<\/span><\/p>\n

Kiwi    60 <\/span><\/p>\n

 <\/p>\n

The corresponding SQL is:<\/p>\n

SELECT  Product.Name, SUM(OrderItem.Quantity * Product.Price)<\/b> AS Sum<\/span><\/p>\n

FROM    OrderItem INNER JOIN<\/span><\/p>\n

        Product ON OrderItem.ProductId = Product.id<\/span><\/p>\n

GROUP BY Product.Name<\/b><\/span><\/p>\n

 <\/p>\n

Total Money Spent<\/b><\/p>\n

Here is the command that produces this total:<\/p>\n

$customerProducts<\/span> |<\/span> ForEach-Object<\/span> `<\/span><\/p>\n

                           -Begin<\/span> {$total<\/span>=<\/span>0<\/span>;} `<\/span><\/p>\n

                           -Process<\/span> {$total<\/span>+=[<\/span>int<\/span>]<\/span>$_<\/span>.<\/span>Price*<\/span>$_<\/span>.<\/span>Quantity} `<\/span><\/p>\n

                           -End<\/span> {$total<\/span>}<\/span><\/p>\n

 <\/span><\/p>\n

 <\/p>\n

Here is the corresponding SQL:<\/p>\n

SELECT  SUM(OrderItem.Quantity * Product.Price)<\/b> AS Sum<\/span><\/p>\n

FROM    OrderItem INNER JOIN<\/span><\/p>\n

        Product ON OrderItem.ProductId = Product.id<\/span><\/p>\n

 <\/p>\n

 <\/p>\n

Lucio Silveira[MSFT]<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Joining Multiple Tables, Grouping And Evaluating Totals This is a continuation to the Join-Object blog post considering multiple joins and SQL style grouping and total evaluation. Problem: In the Join-Object blog we’ve seen that relational data naturally organizes in multiple tables. Once we have those tables, we frequently want to see the information back together, so we […]<\/p>\n","protected":false},"author":600,"featured_media":13641,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2191","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell"],"acf":[],"blog_post_summary":"

Joining Multiple Tables, Grouping And Evaluating Totals This is a continuation to the Join-Object blog post considering multiple joins and SQL style grouping and total evaluation. Problem: In the Join-Object blog we’ve seen that relational data naturally organizes in multiple tables. Once we have those tables, we frequently want to see the information back together, so we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/2191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/users\/600"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/comments?post=2191"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/2191\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media\/13641"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media?parent=2191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/categories?post=2191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/tags?post=2191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}