6. Functions

Within SQL we have some features, or tools, that let us perform interesting operations without leaving the language: minimum value, maximum value, count, calculate the average, add up or create aliases. These are called aggregate functions, or in English aggregate functions.

MIN()

Returns the lowest value of a certain column.

Which is the lowest invoice?

SELECT MIN(Total) FROM Invoice;

0.99

MAX()

Returns the highest value of a certain column.

Which is the highest invoice?

SELECT MAX(Total) FROM Invoice;

25.86

Who paid it?

SELECT FirstName, LastName FROM Customer WHERE CustomerId = (SELECT CustomerId from Invoice WHERE Total IN (SELECT MAX(Total) FROM Invoice));

Helena  Holý

Thanks, Helena! You're paying for my kids' college.

COUNT()

It helps us count the number of results.

How many invoices do I have from 2013?

SELECT COUNT(Total) FROM Invoice WHERE InvoiceDate LIKE '2013-%';

80

AVG()

Calculates the average. In other words, it adds up all the elements and divides them by their number.

What is the average spending per user?

SELECT AVG(Total) FROM Invoice;

5.65

SUM()

Adds up the values of a column.

How much money have I billed in total?

SELECT SUM(Total) FROM Invoice;

2328.6

Did I bill more in 2012 or 2013?

SELECT SUM(Total) FROM Invoice WHERE InvoiceDate LIKE '2012-%';

477.53

SELECT SUM(Total) FROM Invoice WHERE InvoiceDate LIKE '2013-%';

450.58

Oops! We're losing money.

alias

When we have many columns, with names closer to a robot's mind than to its creator's, (human) errors may start to appear.

For example.

SELECT TrackId FROM InvoiceLine WHERE InvoiceId = 4;

TrackId
42
48
54
60
66
72
78
84
90

We can improve it thanks to aliases, by adding AS and the new temporary name.

SELECT TrackId AS Song FROM InvoiceLine WHERE InvoiceId = 4;

Song
42
48
54
60
66
72
78
84
90

Even with other columns.

SELECT InvoiceLineId AS Id, TrackId AS Song, UnitPrice AS Price FROM InvoiceLine WHERE InvoiceId = 4;

Id | Song | Price
13  42  0.99
14  48  0.99
15  54  0.99
16  60  0.99
17  66  0.99
18  72  0.99
19  78  0.99
20  84  0.99
21  90  0.99

Working with dates in SQLite

SQLite does not have a specific data type for dates, but it allows you to store them in different formats:

• TEXT: ISO 8601 format: '2025-01-28 14:30:00'
• INTEGER: Unix timestamp (seconds since 1970-01-01)
• REAL: Julian days

The most common and recommended format is TEXT with ISO 8601, since it is readable and easy to manipulate with functions like strftime().

Creating a view to combine aggregate functions

Imagine that we want to create a view that shows us a summary of the invoices (Invoice) by year. This view will include the year, the total number of invoices, the total billed, the highest invoice and the lowest invoice for each year.

CREATE VIEW resumen_facturas_por_anio AS
SELECT
    strftime('%Y', InvoiceDate) AS Anio,  -- Extract the year from the date
    COUNT(InvoiceId) AS Total_Facturas,   -- Count the number of invoices
    SUM(Total) AS Total_Facturado,        -- Sum up the total billed
    MAX(Total) AS Factura_Mas_Alta,       -- Get the highest invoice
    MIN(Total) AS Factura_Mas_Baja        -- Get the lowest invoice
FROM
    Invoice
GROUP BY
    strftime('%Y', InvoiceDate);          -- Group by year

• Extracts the year from the InvoiceDate column using strftime('%Y', InvoiceDate).
• Counts the number of invoices per year with COUNT(InvoiceId).
• Sums the total billed per year with SUM(Total).
• Gets the highest and lowest invoice per year with MAX(Total) and MIN(Total).
• Groups the results by year using GROUP BY.

Now that we have created the view, we can query it easily to get the summary of invoices by year:

SELECT * FROM resumen_facturas_por_anio;

Anio | Total_Facturas | Total_Facturado | Factura_Mas_Alta | Factura_Mas_Baja
2009    83  449.46  13.86   0.99
2010    83  481.45  21.86   0.99
2011    83  469.58  21.86   0.99
2012    83  477.53  23.86   0.99
2013    80  450.58  25.86   0.99

This example shows how views can be a powerful tool to combine SQL functions and simplify complex queries: it simplifies data queries, avoids code repetition and is easier to maintain.

We are always working with a single table. What happens if we want to combine data from several tables? For example, if we want to show the customer's name along with the invoice summary. How can we do it?

In the next lesson, we will learn how to do it with JOIN.

Activity 1

From the Track table, get the following information.

1. What is the title of the song that weighs the least (Bytes).
2. What is the title of the song that lasts the longest (Miliseconds).
3. How many songs cost 1$ or more.
4. How many songs there are by Queen.
5. What is the average duration among all the songs.
6. What is the average weight among all the U2 songs.
7. How many songs have Bill Berry as Composer (Composer).
8. One Mb is: Bite / 1024 / 1024. Show all the Tracks calculating, and renaming, the Bytes column to Mb.

Solutions