Skip to content

Refining Selections

Now that you know the joins, it's time to learn how to sort the output data, limit how much data we can get back, combine data, count, sum the data, and search for similarities in strings.

Getting Started

For these exercises, we will be continuing with the gameStore database. You can just use the existing database if you still have it, or download and import it using these commands:

First, create the database gamestorejoins if it does not yet exist:

MySQL
CREATE DATABASE gamestorejoins;

Then, download and import the gameStore database (from unix terminal):

Text Only
curl -o gamestore.sql https://raw.githubusercontent.com/samk-ai/relational-databases-public/main/databases/gamestore.sql
mysql -p gamestorejoins < gamestore.sql

Then, just open mysql CLI and use the database:

MySQL
USE gamestorejoins;

Sorting Data (Order By)

Data can be sorted by adding the keyword ORDER BY and defining the column sorting order.

Example usage of how to sort all customers by their first name (ascending):

MySQL
SELECT * FROM customers ORDER BY firstName asc;

or by email (descending):

MySQL
SELECT * FROM customers ORDER BY email DESC;

Or to get the customers in order that last created is sorted as first, we could do:

MySQL
SELECT * FROM customers ORDER BY customerId DESC;

Limiting Data (Limit)

What if we only wanted to get for example the last customer that was added? Or we would want to get the the last 3 orders made? For this, we can use the LIMIT keyword to limit the amount of data that we get back.

So, to get only the last customer that was added into our customers table, we can do this:

MySQL
SELECT * FROM customers ORDER BY customerId DESC LIMIT 1;

Or if we would want to get the last 3 orders, we could do this:

MySQL
SELECT * FROM orders ORDER BY orderId DESC LIMIT 3;

Combining Data (Concat)

With SQL it is also possible to combine data into one using the function concat. For example, to combine the firstName and lastName of the user into one field called name, we could do this:

MySQL
SELECT concat(firstName, " ", lastName) AS name, email FROM customers;

Counting the Results (Count)

The function count can be used to count the number of results. For example, to count the number of orders, we could do this:

MySQL
SELECT COUNT(orderId) AS orderAmount FROM orders;

Or for example to get all orders that have gameId set as 2:

MySQL
SELECT COUNT(orderId) AS orderAmount FROM orders WHERE gameId = 2;

Calculating Sum for Fields (Sum)

What if we wanted to sum up prices of all the orders that have been made? Or calculate sum of all orders made by customer with ID 3? This is possible with the sum function.

To sum up prices of all the orders in the orders table, we can do this:

MySQL
SELECT sum(games.price) AS price FROM orders
    RIGHT JOIN games
        ON games.gameId = orders.gameId;

Or to get all orders made by customer with an ID of 3:

MySQL
SELECT sum(games.price) AS price, orders.customerId FROM orders
    RIGHT JOIN games
        ON games.gameId = orders.gameId
    WHERE orders.customerId = 3;

Like (Like)

Right now we have only searched for specifid data, for example users with name exactly Lauren. But what we want to get all the users that have specific email address? Or we partly remember some user's name and would like to search with that partial search?

To solve this problem, we can use the LIKE operator.

As an basic example, to find all customers that have email address ending with @mymail.com, you could do:

MySQL
SELECT * FROM customers WHERE email LIKE "%@mymail.com";

Notice the percentage % symbol used. This percentage character in LIKE searches means "match zero, one or multiple times". So in the example above, we match any characters (or none) and the rest of the email address needs to be in format @mymail.com.

In order to search for customers that, for example, have mitser in some part of their last name, you could do this:

MySQL
SELECT * FROM customers WHERE lastName LIKE "%mitser%";

Conclusion

  • Data can be sorted with ORDER BY fieldname ASC|DESC
  • Data can be limited with LIMIT amount
  • Data can be combined with concat
  • Number of records can be count with count()
  • Sum of field values can be counted with sum()
  • Similar string searches can be done with LIKE

Learn More