For the best use of aggregates. I have tried to apply multiple group by on multiple columns.

Let a example I have 2 entities

1. Customer (ID, Name, DOB, Address)

2. Orders (ID, OrderDate, CustomerID, OrderAmount)


I tried to join both the entities by with or without join with Customer and Orders.


I tried to apply the group by on Customer ID, Name, and Sum(orderAmount)

Can I use multiple group on columns.

Or how and what is the best example to use multiple group by. 

Solution

Hi Manish,

The Group By in Aggregates works the same as GROUP BY in SQL. That is, everything you apply a Group By to will appear in the output, and all unique values will appear. Also, all aggregate functions like Sum, Count etc. will appear in the output, but of course not the unique values.

You seem to want the total order amount per customer, and both the customer ID and the name of the customer. In that case, you indeed need a Group By on Customer ID and Name, and the Sum of orderAmount, so it seems you did it right. Do you get the right information?

As for the Join, with "Only With", you include only customers that actually have an order, with a "With or Without", you also include customers without an order (so there will be customers with a sum of 0).


Solution