Run calculation not working in sub query, “does not exist”

PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit 

I have a query that provides the count of 2 things over 2 time periods:

  • time period of this week, start of the week (Sunday/Monday midnight) -> today
  • time period of last week, start of last week (Sunday/Monday midnight) -> today but last week

What I would like is to also perform a calculation on the two "car" count ints that are returned and provide this with the query result.

I can of course do this in the consuming app, but I’d love to be able to do it in my SQL.

Working SQL

This is the base SQL and is what I currently have and working:

WITH last_week AS (   SELECT COUNT(car_name) as car_count_last_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')   AND car_time <= CURRENT_DATE - INTERVAL '6 days' ), current_week AS (   SELECT COUNT(car_name) AS car_count_current_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE)   AND car_time <= CURRENT_DATE ) SELECT car_name,   date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,   CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,   date_trunc('week', CURRENT_DATE) AS start_of_current_week,   CURRENT_DATE AS today,   car_count_last_week,   car_count_current_week   FROM car_store   CROSS JOIN last_week, current_week   WHERE car_name = 'awesome'   GROUP BY car_name, car_count_last_week, car_count_current_week   ORDER BY car_name; 

Setup DB table

CREATE TABLE IF NOT EXISTS car_store (      car_id INT GENERATED ALWAYS AS IDENTITY,      car_time TIMESTAMP NOT NULL,      car_name VARCHAR(255) NOT NULL,      PRIMARY KEY(car_id)   )  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594'); INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');  

Thing that causes error

I would like add the following code – to calculate difference_between_weeks:

ROUND(car_count_current_week/(car_count_last_week/100) - 100)     AS difference_between_weeks 

I have tried calculating in the columns, I have tried adding another sub query. But I don’t seem to be able to calculate the difference_between_weeks as it tells me "does not exist" or ERROR: division by zero

Full SQL that errors

An example of the SQL with the added code that errors:

WITH last_week AS (   SELECT COUNT(car_time) as car_count_last_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')   AND car_time <= CURRENT_DATE - INTERVAL '6 days' ), current_week AS (   SELECT COUNT(car_time) AS car_count_current_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE)   AND car_time <= CURRENT_DATE ) SELECT car_name,   date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,   CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,   date_trunc('week', CURRENT_DATE) AS start_of_current_week,   CURRENT_DATE AS today,   car_count_last_week,   car_count_current_week,   ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks   FROM car_store   CROSS JOIN last_week, current_week   WHERE car_name = 'awesome'   GROUP BY car_name, car_count_last_week, car_count_current_week   ORDER BY car_name; 

Returned error

I get the following error:

ERROR:  division by zero SQL state: 22012 

I feel like I am close but I also feel this may not be possible? Any pointers to what I am missing would be hugely appreciated.

Please let me know if I have missed something?

Thanks