1

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.

enter image description here

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)
GMB
  • 216,147
  • 25
  • 84
  • 135
Maks
  • 113
  • 3

1 Answers1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you! But the query gives results not rounded to zero or five. Here is the result. (304.1, 212.40000000000003, 16.1, 22) (360.5, 259.0, 18.9, 1) (359.0, 259.0, 24.9, 4) (304.3, 197.5, 16.1, 1) (360.19999999999993, 234.90000000000003, 22.9, 5) And it unfortunately does not match what is shown in the image. – Maks Nov 10 '22 at 14:05
  • @Maks: so it means that your values are not of datatype `int`, but some other numeric datatype (`numeric`, `float`, ...). You can cast to an `int` to get the expected behavior, like `width::int / 5 * 5` – GMB Nov 10 '22 at 16:28