Splitting Query Results into Ranges

Aug 4, 2020 by Robert Gravelle

Grouping query results into buckets of equal size is a common requirement for database developers and database administrators (DBAs) alike. Examples include:

customers whose last names begin with A – L and M-Z
products prices that are between 1 – 10 dollars, 11 – 20 dollars, 21 – 20 dollars, etc…
quarterly sales, i.e., from Jan – Mar, Apr – Jun, Jul- Sep, Oct – Dec

Standard SQL is well suited to this task. By combining the power of the CASE statement with the GROUP BY clause, data can be broken up into whatever range we deem necessary to best interpret our data. In today’s blog, we’ll compose a couple of range queries in Navicat Premium‘s excellent Query Editor.

Our first example will require a table containing the grades of several students. Here’s the SQL to create and populate the grade table:

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `StuID` int(11) NULL DEFAULT NULL,
  `Semester` tinyint(4) NULL DEFAULT NULL,
  `YEAR` int(11) NULL DEFAULT NULL,
  `Marks` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 1, 2018, 66);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 3, 2018, 77);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 2, 2018, 86);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (110, 4, 2018, 69);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 1, 2018, 20);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 2, 2018, 39);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 3, 2018, 65);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (100, 4, 2018, 70);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 1, 2018, 50);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 2, 2018, 45);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 3, 2018, 90);
INSERT INTO `grade`(`StuID`, `Semester`, `YEAR`, `Marks`) VALUES (99, 4, 2018, 96);

Here is the grade table in Navicat:

grade_table (89K)

Let’s say that we wanted to count students’ grades by equal percentile quadrants as follows:

0 to 25
26 – 50
51 to 75
76 to 100

Here’s the query to do that, along with the results generated:

student_marks (60K)

Pay attention to the CASE statement and you’ll notice that it defines each ranges using the BETWEEN operator. It selects values within the inclusive range, meaning that the outer values are included in the range. BETWEEN works with many types of data, including numbers, text, and dates.

In many cases, dates can be split into logical segments using one of the DATE type’s many date part functions, such as DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), MONTH(), YEAR(), etc. These allow you to break up your ranges by intuitive units.

To demonstrate, here’s a query in MySQL using the Sakila Sample Database that calculates the average rental cost for each customer, broken down by year and month:

average_rental_cost (163K)

The advantage to using DATE functions is that they allow us to dispense with the CASE statement because we can GROUP BY the same functions.

In today’s blog, we learned how to write range queries using Navicat Premium‘s excellent Query Editor. Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!