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?
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;