Allocate groups by size, preliminarily rounded and grouped

Question

You are given a database of notebooks that contains two tables.

• the table notebooks\brand contains data about the name of notebook brands.
• the table notebooks\notebook contains data about the name of the notebook, its diagonal, width, depth, and height, and has a link to the brand to which this model belongs.

You need to select groups of notebooks by size. To do this, the size should first be rounded up to the nearest 0 or 5 and then grouped by the same size by counting the number of laptops in each group. Sort the data by size.

I Wrote a query that calculates how many laptops are represented in each brand:

cursor.execute("""SELECT brnd.title,
                    COUNT(brnd.id)
                    FROM notebooks_notebook AS ntbk
                    JOIN notebooks_brand AS brnd
                    ON ntbk.brand_id = brnd.id
                    GROUP BY brnd.title """)

('HP', 225)
('Prestigio', 1)
('Huawei', 6)
('ASUS', 223)
('Haier', 2)
('Xiaomi', 13)
('MSI', 34)
('HONOR', 15)
('Gigabyte', 5)
('Digma', 4)
('Lenovo', 253)
('Dell', 75)
('Acer', 82)
('Chuwi', 4)
('Apple', 55)

Answer 1

Postgres does integer division. Assuming that your size columns are defined as integers, we can round to the nearest 5 with an expression like :

width / 5 * 5

We can apply this logic to your query ; starting from your existing joins, we can compute the rounded values in a lateral join, then aggregate

select x.width, x.depth, x.height, count(*) cnt
from notebooks_notebook n
inner join notebooks_brand as b on n.brand_id = b.id
cross join lateral (values (width / 5 * 5, depth / 5 * 5, height / 5 * 5)) x(width, depth, height)
group by x.width, x.depth, x.height