Is there a 'mode' function in spatial join QGIS

Question

I am trying to find the most number of 'bands' (band A, B, C) in each postcode area. The 'bands' is represented in points and I would like to output into maps(polygons). Is there a way to do a spatial join (i.e. join attributes by location) and get it to calculate the mode (most times that reoccurs). Using QGIS 2.18.13

Answer 1

In this example I used refFunctions plugin and a Virtual Layer.

Sample Data

• One point layer (bands_pt.shp) with id, bands fields; 8 records.
• One polygon layer (postcodes_poly.shp) with id, postcode fields; 2 records.

Workflow

(1) Copy postcode field from the polygon layer to the point layer; which falls within the designated polygon (ie postcode 1234 and 5678).

1. Open attribute table of point layer (bands_pt).
2. Create a new text field postcode = geomwithin('postcodes_poly', 'postcode').

(2) Create a Virtual Layer

1. Layer | Add Layer | Add/Edit Virtual Layer
2. [Import] bands_pt

A sample query is:

SELECT bands, postcode, max(cnt)
FROM(SELECT postcode, bands, count(*) AS cnt
     FROM bands_pt
     GROUP BY postcode, bands)
GROUP BY postcode

If you open the attribute table of this virtual layer it will show combination of most frequent band (and its count) in each postcode area.

EDIT (as requested)

To combine the Mode value with the postcodes polygon, try following query:

SELECT *
FROM(SELECT postcode, bands, max(cnt)
     FROM(SELECT postcode, bands, count(*) AS cnt
          FROM bands_pt
     GROUP BY postcode, bands)
GROUP BY postcode) AS a 
INNER JOIN postcode_poly
ON a.postcode = postcode_poly.postcode

Before changing the syntax, please do not forget to [import] postcodes_poly layer along with the bands_pt (as already imported in the above step (2)-2).