Mathematical Operations Date Time Functions

It is a function that returns the number of rows with the given value in parentheses. Counts records in the database.

Query that returns how many rows are in a table:

select count(*) from film;

Use it with a WHERE condition;

SELECT COUNT(*) from city where country_id=2 ;

SELECT COUNT(*) as total from payment
where amount > 8.99 ;

SELECT COUNT (*) result FROM rental
WHERE staff_id = 1
AND
customer_id = 3 ;

SELECT COUNT (*) as result FROM rental
WHERE staff_id = 1
OR
customer_id = 3 ;

SELECT staff_id , COUNT (payment_id) FROM payment
GROUP BY staff_id;

The query finds and groups the staff_id values in the payment table. Calculates the number of rows that have these values.

It gives the same result as the query below because there is no duplicate record (unique) in the payment_id column.

SELECT staff_id , COUNT (*) FROM payment GROUP BY staff_id;

Here is the query that returns the number of films with the return_date between 2005-05-26 and 2005-05-30:

SELECT COUNT (*) result FROM rental WHERE return_date BETWEEN
'2005-05-26' and '2005-05-30' ;

sum

It is the function that sums the values in the specified field.

The query that returns the sum of payments in the payment table:

SELECT SUM(amount) FROM payment ;

Usage with WHERE condition:

SELECT SUM(amount) FROM payment
where customer_id = 341
and
staff_id = 2;

SELECT SUM(amount) FROM payment
where customer_id = 341
OR
staff_id = 2;

SELECT SUM(amount) FROM payment
WHERE customer_id NOT IN (341, 342, 343, 344) ;

SELECT SUM(amount) as total FROM payment
WHERE customer_id NOT IN
( SELECT customer_id from payment
where customer_id BETWEEN 341 and 402) ;

The subquery contains customer_id values between 341 and 402. Amount values of customer_id values other than these values (NOT IN) are summed.

SELECT SUM (amount) as Total FROM payment WHERE staff_id != 1 ;
SELECT SUM (amount) as Total FROM payment WHERE staff_id != 2 ;

1.  The query lists staff_id values not equal to 1

2. The query collects the amount values of the rows whose staff_id value is not equal to 2.

avg

It is the function that lists the average of the values in the specified field.

SELECT AVG(amount) average FROM payment ;

SELECT AVG(amount) average FROM payment
WHERE payment_id BETWEEN 22430 and 22436 ;

SELECT staff_id, AVG(amount) FROM payment GROUP BY staff_id ;

max-min

It is the function that helps us to find the highest (MAX) or lowest (MIN) value in the specified column.

SELECT MAX(amount) - MIN(amount) as difference from payment;

SELECT rental_id, MIN(amount) FROM payment
GROUP BY rental_id
ORDER BY rental_id DESC;

abs

It is the function that saves the numeric value from negativity.

SELECT abs (-5);

ceil

A function that rounds a decimal value to a higher number

SELECT ceil(amount), payment_id FROM payment WHERE amount > 5;

floor

It is a function that rounds a decimal value to a lower number.

SELECT floor(amount), payment_id, customer_id FROM payment
WHERE amount > 5;

date – time functions

To learn the current date;

SELECT Current_Date;

To find out the current time;

SELECT Current_Time;

To find out the current time;

SELECT now();

To find how many years, months and days are the difference between today’s date and the date we entered;

SELECT age(Timestamp '2019-12-01');

Example: To find the difference between the values in the date column and today’s date from the table;

SELECT rental_date, customer_id, age(now(), rental_date) from rental ;