BBampaa

Lessons › SQL › Deep Dive: Text, Date & Number Functions

SQL lesson

CEIL() and FLOOR() for rounding up and down

CEIL always rounds a decimal up to the next whole number, and FLOOR always rounds it down - unlike regular rounding, which just picks whichever whole number is closer and can go either direction.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Rounding up and down to whole numbers

SELECT product_name, CEIL(price) AS sticker, FLOOR(pallet_count) AS pallets FROM products

Result:

product_namestickerpallets
Wireless Earbuds803
Travel Mug250
Cotton Tee159
Hiking Backpack902

Step by step

  1. Run the query without CEIL so you can see what the unfiltered values look like.
    SELECT product_name, price, FLOOR(pallet_count) AS pallets FROM products;
  2. CEIL rounds up to the nearest integer.
    SELECT product_name, CEIL(price), FLOOR(pallet_count) AS pallets FROM products;
  3. AS gives the CEIL result a friendly column name so the output reads cleanly.
    SELECT product_name, CEIL(price) AS sticker, FLOOR(pallet_count) AS pallets FROM products;

Practice

Try each one in your head first, then open the answer.

1. Write a query on the orders table that returns the revenue column and a column called revenue_up with each revenue rounded up to the nearest whole number.
SELECT revenue, CEIL(revenue) AS revenue_up FROM orders
2. Write a query on the store_sales table that returns the revenue column and a column called revenue_up with each revenue rounded up to the nearest whole number.
SELECT revenue, CEIL(revenue) AS revenue_up FROM store_sales
3. Write a query on the products table that returns the price column and a column called price_down with each price rounded down to the nearest whole number.
SELECT price, FLOOR(price) AS price_down FROM products
4. Write a query on the reviews table that returns the rating column and a column called rating_down with each rating rounded down to the nearest whole number.
SELECT rating, FLOOR(rating) AS rating_down FROM reviews
5. Write a query on the employees table that returns the salary column and a column called salary_down with each salary rounded down to the nearest whole number.
SELECT salary, FLOOR(salary) AS salary_down FROM employees

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed