Structured Query Language (SQL)

There are a lot of times when you want to just count things. For example, how many employees do we have? This can be accomplished by using the MySQL count(*) function.

mysql> select count(*) from names;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.01 sec)

I tend to add an as statement for a count, so I can reference the column easily:

mysql> select count(*) as cnt from names;
+-----+
| cnt |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)

The count(*) function is extremely useful in a lot of different ways. For example, when you want to include how many search results you found from a search. It's also useful when you are doing pagination aka showing only 20 results within 100. To do pagination correctly, you need to know how many records there are so you know how many pages that need to be generated.

What if we wanted to calculate some numbers coming from our database? We can use the MySQL function, sum().

Let's say we want to calculate the total amount we're spending on salaries. First, we need to add a salary column to our names table:

alter table names add salary decimal(10,2) null;

mysql> describe names;
+-----------+---------------+------+-----+-------------------+-------------------+
| Field     | Type          | Null | Key | Default           | Extra             |
+-----------+---------------+------+-----+-------------------+-------------------+
| id        | int unsigned  | NO   | PRI | NULL              | auto_increment    |
| started   | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| lastName  | varchar(252)  | NO   |     | NULL              |                   |
| firstName | varchar(252)  | NO   |     | NULL              |                   |
| salary    | decimal(10,2) | YES  |     | NULL              |                   |
+-----------+---------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

Now, let's add salaries for each employee:

update names set salary=120000.01 where id=1;
update names set salary=120000.01 where id=2;
update names set salary=65000.28 where id=3;
update names set salary=78000.44 where id=4;
update names set salary=96468.97 where id=5;
update names set salary=65732.86 where id=6;
update names set salary=1.99 where id=7;

Now, let's craft a statement that will give us the total salary for all employees:

mysql> select sum(salary) as totalSalary from names;
+-------------+
| totalSalary |
+-------------+
|   545204.56 |
+-------------+
1 row in set (0.00 sec)

What's happening here is, rather than giving back a set of seven rows, it adds each salary column and returns the final number in one row.

We can also fine tune this. Let's say we only want to sum high earners:

mysql> select sum(salary) as totalSalary, count(*) as employees from names where salary > 100000;
+-------------+-----------+
| totalSalary | employees |
+-------------+-----------+
|   240000.02 |         2 |
+-------------+-----------+
1 row in set (0.01 sec)

Looks like we have two employees that earn over $100,000 a year.

What if we wanted to find the total salary for employees in a certain state? We would use a join in this situation:

mysql> select sum(salary) as totalSalary, count(*) as employees from names n left join addresses a on n.id=a.nameId where
state="CA";
+-------------+-----------+
| totalSalary | employees |
+-------------+-----------+
|   305732.88 |         3 |
+-------------+-----------+
1 row in set (0.00 sec)

We have three employees in the state of CA, and they all earn $305,732.88.

Finally, we want to figure out how many employees we have in California and how many employees are high earners:

mysql> select sum(salary) as totalSalary, count(*) as employees from names n left join addresses a on n.id=a.nameId where state="CA" and salary > 100000;
+-------------+-----------+
| totalSalary | employees |
+-------------+-----------+
|   240000.02 |         2 |
+-------------+-----------+
1 row in set (0.00 sec)

We have two employees that make over $100,000 and live in CA.

You can do more extensive math in these scenarios. We'll look at more examples in later lessons.

SQL

Session Prices

Scheduling