What is exactly meant by "set of values" in MySQL?

Question

A friend of mine asked me to sum up a few numbers.

Instead of using a calculator, I decided to write a SQL statement. The statement I wrote was:

select sum(100,300,200);

Then, I got an error. I jumped to MySQL docs, where it says:

Aggregate functions operate on sets of values.

If [100,300,200] isn't a set of values, then what is?

Answer 1

It means sets of values from multiple rows.

mysql> select * from mytable;
+------+
| num  |
+------+
|  100 |
|  200 |
|  300 |
+------+
3 rows in set (0.00 sec)

mysql> select sum(num) from mytable;
+----------+
| sum(num) |
+----------+
|      600 |
+----------+

If you want to sum up three scalars in one expression, just use +.

mysql> select 100+300+200 as sum;
+-----+
| sum |
+-----+
| 600 |
+-----+

---

Re your comment:

Then why I can't use subquery results in aggregate functions? Something like: select c.first_name, c.last_name from customer c where 20 = count(select customer_id from rental r where r.customer_id = c.customer_id));

Well, you can't just make up syntax and expect it to work. No programming language works like that!

You can do this:

select c.first_name, c.last_name from customer c 
where 20 = (
  select count(*) from rental r 
  where r.customer_id = c.customer_id);

You can also do this:

select c.first_name, c.last_name from customer c
join rental r on r.customer_id = c.customer_id
group by c.customer_id
having count(*) = 20;