¿What are calculation queries?
They are queries where we can calculate fields individually, that is you can calculate all the rows that are inside a field and you'll get only the result of all the rows inside a field, so the main characteristic of a calculation query is that we get a new field where we have the calculations performed in a field.
¿What do I need to know about making a calculation query?
In each RDBMS we have different functions, that is what we call a unique dialect for each RDBMS, but there are functions that are frequently in all RDBMS, functions that both MySQL and for example Access can support.
These functions are:
Now()
With this function, we can get the exact moment when we do the query, so we get the now.
Datediff()
We can know the difference between dates. It takes 2 parameters, the first parameter will be the youngest date and the second will be the oldest date
Date_format()
We can set a format for dates. It receives 2 arguments, the date, and the new format, for write a format, we have to write it inside "quotes" and we use
"%Y" for the year
"%M" for the month
"%D" for the day
Round()
We can round numbers in the case that we have decimals, floats, etc. It takes two parameters, the number we want to round like the first parameter and the number of decimals until we want to round like the second parameter.
Concat()
We can concatenate values, most used with values text type.
Extra Information
Differences between order by, group by, where and having
Order by, group by, and where
How order by, group by, and where, can process fields before they were processed, what means this? this means that we can order by any field, we can group by any field and we can set criteria with "where" with any field, so we have total liberty
Having
The main difference between having and the clauses that previously I told you is that having it just can set criteria when a field is already processed