# Simple Window Function

October 8, 2012

Here's a simple window function, used to work out a count of rows.

`bands_band` rows have a link to a `regions_region` row.

These two queries produce the same results. First, a simple `count`:

```SELECT r.name, count(b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON (r.id = b.region_id)
GROUP BY r.name
ORDER BY 2 desc
```

Next, an equivalent query using a window function:

```SELECT DISTINCT ON (r.name) r.name, count(b.region_id) over (PARTITION BY b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON b.region_id = r.id
```

If we want to order this query, we can use a subquery like this:

```SELECT * FROM
(SELECT DISTINCT ON (r.name) r.name, count(b.region_id) over (PARTITION BY b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON b.region_id = r.id) x
ORDER BY 2 desc
```

### So what's the point of the window function?

Using window functions it's possible to aggregate over different things in different parts of the query. `GROUP BY` would refer to the entire query, here we can create a "window" specific to a given result column.

We can use it to produce a list of bands along with the number of bands that are also in that region:

```SELECT b.name, count(*) over (PARTITION BY b.region_id)
FROM bands_band b
```

We can also easily expand this to include the region name:

```SELECT b.name, r.name, count(*) over (PARTITION BY b.region_id)
FROM bands_band b
INNER JOIN regions_region r on (r.id = b.region_id)
```

Bands have a description of the national_grading level on them, held in `b.national_grading`. We can add this column to the results, along with a `count` of other bands with the same grading:

```SELECT b.name,
r.name,
count(*) over (PARTITION BY b.region_id) as region_count,