SQL

Using Functions in SQL

February 29, 2016

SQL has a number of built-in functions that can be used to find common query results, such as averaging data, summing up a column of data, rounding information off, formatting data, etc. SQL also has a number of options for building your own custom functions (and triggering them to run). I usually like to use functions when I’m looking for data and reporting. I don’t like using them in code, as the language I’m performing a task in is typically better suited to manage data than is SQL, comparably.

SQL functions come in three types. Aggregate, Scalar, and Custom. Aggregate functions (those that take multiple objects as an input):

  • AVG() – Show the average value in a defined set of data
  • COUNT() – Output the number of rows that match a pattern
  • FIRST() – Output the first value in a set of objects
  • LAST() – Returns the last value in a set of objects
  • MAX() – Show the largest value given a set of objects
  • MIN() – Output the smallest value of a set of objects
  • SUM() – Output the sum defined objects

Scalar functions, which return a value (such as a number or new text):

  • FORMAT() – Formats data in a field
  • LEN() – Shows the length of text
  • LCASE() – Converts the specified data into lower case
  • MID() – Pulls specified characters from text
  • NOW() – Shows the system date and time
  • ROUND() – Rounds a decimal number to the decimals indicated
  • UCASE() – Converts specified data into upper case

Now let’s look at actually using a SQL function. We’ve covered running a SELECT Statement in SQL. But what if you want to count the number of objects returned with that statement. You could dump the results into a csv and view them in Excel. Or, you could save yourself a little time by using the SQL COUNT() function, which shows the number of rows that match a given criteria. In its most basic form, the COUNT function is used as follows (where column is a column in a database and table is the table the column is in:

SELECT COUNT(column) FROM table;

To put this in action, you can look at all records in the Customers table using the following:

SELECT COUNT(*) FROM Customers;

In this article, we’ll use the same “Customers” table from our first articles to add and edit columns:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

So the output of the first COUNT would be 5. To constrain, you can use WHERE  following the initial COUNT line. For example, the following SQL statement counts the number of customers from “City”=Minneapolis from the “Customers” table:

SELECT COUNT(City) AS InMinneapolis FROM Customers
WHERE City="Minneapolis";

You can also use the DISTINCT option to look at how many unique customer cities we have:

SELECT COUNT(DISTINCT City) AS CitieswithCustomers FROM Customers;