# SQL built-in functions: getting a random voter
Most SQL database systems provide functions you can run inside a query to perform all sorts of things.
For example, in PostgreSQL you can do this:
SELECT RANDOM();
And that gives you a random number between 0.0 and 1.0[1].
SELECT COUNT(id) FROM polls;
We can also use the function, COUNT, to tell us how many rows for the column id exist in polls[2].
You can use COUNT with any row result. For example, COUNT(*) is totally valid and is normally the way the function is used.
Other popular aggregate functions are:
MAX()MIN()AVG()SUM()
You can also create your own functions (opens new window) using SQL (in a very similar way to what we'd do using Python). However, that is a very advanced topic and we won't be looking into it in this course.
However do remember that the reason to define SQL functions is the same as for Python functions: to organise and reuse code. If you find yourself repeating yourself often within a database, you can create a function that will stay in the database and can be used in your queries. Similarly if you create a Python function or variable to execute or store a query definition, sometimes it can be useful to do this in the database instead!
# Using functions in our app queries
We'll need to use a function in our app. That's because in order to get a random person who voted for an option (i.e. the "winner"), we'll use the RANDOM function.
This is how you select a random row from a table in PostgreSQL. We're adding this to database.py:
SELECT_RANDOM_VOTE = "SELECT * FROM votes WHERE option_id = %s ORDER BY RANDOM() LIMIT 1;"
Doing ORDER BY RANDOM() shuffles the rows. Then doing LIMIT 1 just gets the first one, after they've been shuffled.
Easy!
Remember though that this query needs a value to be passed to it.
Let's define our get_random_poll_vote() function too:
def get_random_poll_vote(connection, option_id):
with connection:
with connection.cursor() as cursor:
cursor.execute(SELECT_RANDOM_VOTE, (option_id,))
return cursor.fetchone()