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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I don’t see why you require a CROSS JOIN in this case! They are performance killers!

You can simplify this problem greatly by doing the following (and make it more general into the bargain!) – all code below is available from the fiddle here:

CREATE TABLE IF NOT EXISTS car_store 
(
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP(0) NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id),
     
     car_date DATE     GENERATED ALWAYS AS (car_time::DATE) STORED,
     car_week SMALLINT GENERATED ALWAYS AS (EXTRACT(WEEK FROM car_time)) STORED
);

Note the use of the GENERATED (aka "calculated" or "virtual" fields). PG 12 introduced these, and since you’re running 13, we’re golden!

PG’s implementation only has the STORED storage type for the moment, but when the VIRTUAL type comes along, it would probably be better in this case – although the fields are a mere 6 bytes (4 for DATE and 2 for SMALLINT).

I also added data – because 6 records doesn’t really give a good foundation for any testing!

Sample:

-- Week starting on Mon (19-04) and ending on Sunday (25-04) (poor sales)


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:01:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:02:27'); 


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-20 12:12:00');

-- no sales on Tuesday

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:05:27');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:06:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:07:27'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:08:00');
..
..  ~ 60 more records snipped...
..

What I did then was:

SELECT 
  cwk,
  c_name,
  LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid'
  END AS status

FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
ORDER BY cwk DESC, c_name;

Result:

cwk c_name  last_wk_sales   this_week_sales   status
19  awesome            16                17   Figures week to date
18  awesome            22                16   Figures valid
17  awesome            12                22   Figures valid
16  awesome     NULL                     12   *** No previous sales for period***

Note the use of the LAG() PostgreSQL WINDOW function – these are very powerful and well worth getting to know – they will repay time and effort spent learning them many times over… LAG() is perfect for your use case where you wish to compare last week’s sales with this week’s!

I then added calculations for the percentages:

SELECT 
  cwk,
  c_name,
  LAG(wk_cnt) OVER w AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt) OVER w IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid - period closed'
  END AS status,
  wk_cnt - LAG(wk_cnt) OVER w AS diff,
  ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change
FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
ORDER BY cwk DESC, c_name;

Result (not formatted, except for last two (new) columns):

cwk c_name  last_wk_sales   this_week_sales status      diff     pc_change
19  awesome 16   17 Figures week to date                   1             6
18  awesome 22   16 Figures valid - period closed         -6           -27
17  awesome 12   22 Figures valid - period closed         10            83
16  awesome NULL 12 *** No previous sales for period***     

So, we have our difference in absolute numbers and the percentage change from the previous week

I also added this:

WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)

It’s another method for tidying up your SQL – you don’t have to put that cumbersome WINDOW definition all over the place – just a bit of sugar, but nice to have!

I also took care of the problem @nbk pointed out with the INTEGER division – yes, it can trip people up, but just learn about the issue and move on!

  ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change

I used the REAL type instead of DECIMAL – from here, it’s only 4 bytes and accurate to 6 decimal places – I hardly think more than that will be required for this use case. Also, from that page:

However, calculations on numeric values are very slow compared to the
integer types, or to the floating-point types described in the next
section.

You can get rid of the cumbersome CASE WHEN LAG(wk_cnt) OVER w IS NULL bits by putting the lot in a sub-query as follows:

SELECT 
  *, 
  (TO_DATE(cyr::TEXT || cwk::TEXT, 'YYYYWW') + INTERVAL '3 DAY')::DATE AS "Week starting:"
FROM
(
  SELECT 
...
... body of query snipped - see fiddle - it is the same as above
...
  ) AS tab
  WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
) AS tab2
WHERE last_wk_sales IS NOT NULL -- could also add dates between...
ORDER BY cwk DESC, c_name;

You mightn’t even need this – if you always sell at least one car a week – otherwise, you can JOIN with a calendar table as shown here.

Performance with the sub-query doesn’t appear to suffer too much (see fiddle), although I would urge you to test various indexes with your own data and h/ware setup – although, with car sales, the number of records isn’t likely to be huge.

Method 2

Your error was that Postgres interpreted the column as Integer and performs a Euclidean division, which leads to the zero encountered.

Changing the car_count_last_week to a decimal, will give you the rational numbers division

 (car_count_last_week::decimal/100)

fixes the problem

CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_date  TIMESTAMP,
     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');  
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
  ,
  
 CASE WHEN car_count_last_week = 0 then 1
 ELSE ROUND(car_count_current_week/(car_count_last_week::decimal/100)) * 100 
 END 
   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;
car_name | start_of_last_week  | today_but_last_week | start_of_current_week  | today      | car_count_last_week | car_count_current_week | difference_between_weeks
:------- | :------------------ | :------------------ | :--------------------- | :--------- | ------------------: | ---------------------: | -----------------------:
awesome  | 2021-05-03 00:00:00 | 2021-05-09 00:00:00 | 2021-05-10 00:00:00+01 | 2021-05-15 |                   1 |                      4 |                    40000

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply