Calculate Mode of points that fall within each polygon

• I have two tables in SQL Server.
• Address Points & District Regions
• Both have geom fields and share common SRID
• The Address Points have an integer value field.

For each district I want to take the mode of this value field, from all of the address points that fall within it.

I have managed to get an update to calculate the average value. But I need the mode. I need to know which is the most recurring value for each district

UPDATE 
        DISTRICTS 
    SET AverageOfValue = (
        SELECT Avg(Address_Value)
            FROM Address A with (index(OBJECTID))
        WHERE DISTRICTS.Shape.STIntersects(A.Shape) = 1 AND Address.Address_Value is not null)

I am struggling to try and adapt this to calculate the Mode. How would I do this?

e.g. If the following table is a subset of all of the address points that fall within a specific District, the mode value I am looking to extract would be 8, and update a ModeValue field back in the District layer.

+----------+---------------+
| OBJECTID | Address_Value |
+----------+---------------+
|       23 |             8 |
|       29 |             8 |
|       35 |             5 |
|       42 |             4 |
|       44 |             8 |
|       47 |             9 |
|       58 |             8 |
+----------+---------------+

Answer

The mode is the most frequent value. You can use aggregation to calculate this:

UPDATE DISTRICTS 
    SET ModeValue = (SELECT TOP (1) Address_Value
                     FROM Address A with (index(OBJECTID))
                     WHERE DISTRICTS.Shape.STIntersects(A.Shape) = 1 AND
                           Address.Address_Value is not null
                     GROUP BY Address_Value
                     ORDER BY COUNT(*) DESC
                    );