Hands-on SQL Tips: Guard Elements And Window Functions

Home » Blog » Software » Enterprise Software » Hands-on SQL Tips: Guard Elements And Window Functions

Sometimes when displaying data in an application you want the amount of data (rows, individual data points etc.) to be limited for the app to stay responsive and to allow the user to more easily scan and browse the data set.

In SQL this is commonly done using a LIMIT statement:


Now, what if you want to not only display the data but also inform the user in case the total number of rows in the table exceeds the limit set by your application? You could of course select a COUNT of the table rows and display a message if COUNT(*) > 100:


However, this means you have to execute 2 queries instead of 1 which depending on your application architecture could involve additional overhead such as additional HTTP calls in a single-page application architecture.

A more efficient solution is using a guard element to determine if the total number of data points is larger than a given number:


Using 101 instead of 100 as the LIMIT argument in this case allows you to check if there are further rows in addition to the ones selected. If the size of the result set returned by this query is larger than 100 you know that the table contains additional rows without having to execute an additional COUNT query. In terms of data structures, such an element that serves no other purpose but to determine if a list iteration can terminate is called a guard element.

Another useful feature when dealing with large datasets is PostgreSQL’s window function. When using aggregate functions such as SUM or COUNT in SQL you have to use a GROUP BY statement if you want to mix those aggregate columns with normal columns in your result set:

SELECT id, name, location, SUM(amount) FROM items GROUP BY id, name, location

Sometimes this is desired, for example if you want summate over the amounts by item. However, sometimes you want the total sum over all items or you might not want to group by a certain field (for instance ‘location’ in the above example). In that case you’d have to resort to executing 2 queries again: One that yields the individual items and one that gives you the total amount.

Another, more elegant solution in such a use case is using a window function:

SELECT id, name, location, SUM(amount) OVER () FROM items

Windowing allows you to take values from a subset of table rows and use those in calculations. The excellent PostgreSQL documentation explains it like this:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.

By using OVER () with no arguments you create a window over the whole result set and use that to calculate the overall amount. In addition to that OVER () also takes expressions like PARTITION BY depname ORDER BY salary DESC that allow you to group (PARTITION BY) and order the calculation results.

Windowing also allows you to accumulate values that take previous rows into account. The following query for instance yields a result set where accumulated_amount increases for each subsequent row by that row’s amount, which effectively creates a series of the sequence of amounts in the item table:

SELECT name, SUM(amount) OVER (ORDER BY amount DESC) AS accumulated_amount FROM itmes

Along those lines, pivoting in PostgreSQL also is a useful approach (commonly known from Microsoft Excel) for aggregating and partitioning values by doing a matrix transposition and turn rows into columns and vice versa.

Leave a Comment

* Checkbox GDPR is required


I agree

By continuing to browse the site you agree to our use of cookies. Privacy Policy

Privacy Preference Center

Strictly necessary

These cookies are necessary for the site to function.

PHPSESSID: Preserves user session state across page requests.

__cfduid: Used by the content network, Cloudflare, to identify trusted web traffic.



Remembers the user's submitted data when a comment is submitted in a blog post. The purpose is to aut o-populate form fields for subsequent comments, in order to save time for the user.



Statistic cookies help us to understand how visitors interact with our websites by collecting and reporting information anonymously.

_ga: Registers a unique ID that is used to generate statistical data on how the visitor uses the website.

_gat: Used by Google Analytics to throttle request rate.

_gid: Registers a unique ID that is used to generate statistical data on how the visitor uses the website.

collect: Used to send data to Google Analytics about the visitor's device and behaviour. Tracks the visitor across d evices and marketing channels.



We use Wordfence to secure our website against hacking attempts: https://www.wordfence.com/


Close your account?

Your account will be closed and all data will be permanently deleted and cannot be recovered. Are you sure?