If you have that type of list, you may be interested in getting a certain piece of information for each group of records. For our example of employees, you may want to know the number of employees in each department, the number of female employees in each department, the minimum salary of employees in each department, etc. This is different from simply calling an aggregate function on the table because the aggregate function would apply to all records. This time, you want to get the information for each group in the list.

In this and the next lesson, we will use the word "group" or the expression "group of records". The actual word to use is "partition"; and in some cases, we will use "partition". The problem is that the word partition has a particular meaning in the database world and it has to do with "Distributed Databases". Therefore, to reduce confusion, we will mostly use "group" or "group of records" or "groups of records". You are free to substitute "group" with "partition".

1. Open the LambdaPropertiesManagement1 file. Select and copy its whole content
2. Start Microsoft SQL Server and connect
3. Right-click the name of the server and click New Query
4. Paste the LambdaPropertiesManagement1 code in the Query Editor
5. To execute, on the main menu, click Query -> Execute
6. Right-click the name of the server and click Start PowerShell
7. Type SQLCMD and press Enter
8. Type USE LambdaPropertiesManagement1; and press Enter
9. Type GO and press Enter
    
   
10. To see the list of tenants, type the following code and press Enter after each line (if PowerShell is not working, type that code in the Query Editor and press F5 to execute):

    SELECT *
    FROM Rentals.Tenants t;
    GO

    
11. Click inside the Query Editor and press Ctrl + A
12. Type the following code:

    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyNumber, props.Bedrooms, props.MonthlyRate
    FROM Listing.Properties props
    ORDER BY props.Bedrooms;
    GO

    
13. If you observe the records, you would notice that there are six efficiencies (apartments that don't have a formal bedroom) and two of those apartments have the same price, 22 one-bedroom apartments and some of those apartments have the same price, etc.
    Get to the SQL Server Management Studio. Click inside the Query Editor and press Ctrl + A
14. To call an aggregate function, type the following code:

    USE LambdaPropertiesManagement1;
    GO
    
    SELECT MIN(props.MonthlyRate) "Cheapest Monthly Rate"
    FROM Listing.Properties props;
    GO

15. To execute, on the main menu, click Query -> Execute
16. Click inside the Query Editor, press Ctrl + A, and press Delete
17. Return to the PowerShell window
18. You can also call various aggregate functions to get different statistics. To see an example, get to the PowerShell window, type the following statement and press Enter after each line:

    SELECT COUNT(props.PropertyType) "Total Listing",
           MIN(props.MonthlyRate)    "Cheapest Monthly Rate",
           MAX(props.MonthlyRate)    "Highest Monthly Rate",
           AVG(props.MonthlyRate)    "Average Monthly Rate"
    FROM   Listing.Properties props;
    GO

    

As mentioned above, the primary reason to analyse groups of records is to get a piece of information about the groups. This is done using an aggregate function combined with at least the OVER keyword. The primary formula to follow is:

SELECT ... AggregateFunction(WhatField) OVER() ... FROM WhatObject

After the SELECT keyword, you can call an aggregate function applied to a column of the table whose records you want to analyze. After calling the aggregate function, you must call the OVER macro as a function. This means that OVER is followed by parentheses. You can leave them empty or pass some of the options we will learn.

Besides calling an aggregate function, you can also access any field of the table. You can do this before or after the aggregate function (of course, we will see many examples).

Remember that when specifying the fields used in a SQL statement, if a column is a foreign key, you can use a join to get a more significant value from the parent table.

1. When OVER is called as a parameter-less function, the database engine gets the value of the first record and moves to the next record. It compares the value of that new record to the value it has. Depending on the aggregate function that is being used:
   • If the value it has responds better to the aggregate function than the value of the new record, the interpreter moves to the next record
   • If the value of the new record responds better to the function, the new value becomes selected and the interpreter moves to the next record
   When it gets to the last record, the SQL interpreter should have the right value. One of the particularities of the parameter-less OVER macro is that it returns an instance of each record, including records whose value did not conform to the aggregate function.
   To see an example of using the OVER keyword, type the following code and press Enter after each line:

   SELECT MIN(props.MonthlyRate) OVER() "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO

   
2. Notice that, when the parameter-less OVER is called, the SQL interpreter returns all records.
   To make the parameter-less OVER() return only one value, you can apply the DISTINCT keyword. To try it, type the following statement and press Enter after each line (remember that you can press the up arrow key to locate a record and simply edit it):

   SELECT DISTINCT(MIN(props.MonthlyRate) OVER()) "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO

   
3. To use an additonal field, type the following statement and press Enter after each line:

   SELECT props.Bedrooms,
          MIN(props.MonthlyRate) OVER() "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO

4. You can use the GROUP BY clause to an aggregate expression. To try it, type the following statement and press Enter after each line:

   SELECT props.Bedrooms,
          MIN(props.MonthlyRate) OVER() "Cheapest Monthly Rate"
   FROM Listing.Properties props
   GROUP BY props.Bedrooms, props.MonthlyRate;
   GO

If you simply call the parameter-less OVER, it produces the list of all records as they were created. Here is an example:

This result does not show records in groups. As we know already, to show records in groups, you can arrange them, which is done by adding an ORDER BY clause. In this case, specify the ordering in the parentheses of OVER(). The formula to follow is:

SELECT ... AggregateFunction(WhatField) OVER(ORDER BY Field) ... FROM WhatObject

Remember that you want to analyse records in groups of values. Obviously the records should have similar values. And obviously a column that includes unique values (such as a primary key column) or rarely repeating values (like a last name column) is not a good candidate. This means that you should arrange the records based on a column that has repeating values.

1. Return to the Query Editor
2. To arrange records OVER, type the following statement:

   USE LambdaPropertiesManagement1;
   GO
   
   SELECT props.Bedrooms,
          MIN(props.MonthlyRate) OVER(ORDER BY props.MonthlyRate) "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO
   

3. To execute, on the main menu, click Query -> Execute
    
4. In the above example, we arranged the records based on the same columns on which the aggregate function is used. This is not a rule: You can arrange the records based on any column of your choice, but the column should have repeating values. To see an example, change the statement as follows:

   USE LambdaPropertiesManagement1;
   GO
   
   SELECT props.Bedrooms,
          MIN(props.MonthlyRate) OVER(ORDER BY props.Bedrooms) "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO
   
   

5. To execute, on the main menu, click Query -> Execute
6. To see another example, change the statement as follows:

   USE LambdaPropertiesManagement1;
   GO
   
   SELECT props.PropertyType,
          MIN(props.MonthlyRate) OVER(ORDER BY props.PropertyType) "Cheapest Monthly Rate"
   FROM Listing.Properties props;
   GO
   
   

7. To execute, on the main menu, click Query -> Execute
8. To see one more example, change the statement as follows:

   USE LambdaPropertiesManagement1;
   GO
   
   SELECT props.PropertyType,
          COUNT(props.PropertyType) OVER(ORDER BY props.PropertyType) "Range in Category"
   FROM Listing.Properties props;
   GO
   
   

9. To execute, on the main menu, click Query -> Execute
10. Click inside the Query window and press Ctrl + A
11. Press Delete