Skip to content
Advertisement

Count distinct number of customers per fiscal year and display all dates in query result

DB-Fiddle

Expected Result:


In the above result I want to list all order dates and count the number of customers distinct per fiscal year.
The fiscal year starts two months after the calender year and therefore goes from March to February.
(e.g. from 2020-03 til 2021-02).

For example Customer_01 appears the first time on 2020-03-18 within the fiscal year 2020-03 til 2021-02.
Therefore, this order_date gets assigned 1 to it.
If the customer appears again within the fiscal year the next order_date will get assigned 0 to it.


With reference to this question in MariaDB I was able to achieve the expected result as you can see in the DB-Fiddle.

However, now I want to get the same results using postgresSQL.
Therefore, I have modified the query so far to this:

However, now I am getting an error function sum(boolean) does not exist on the SUM(rn = 1) part.
What is the equivalnt syntax for the SUM(rn = 1) in postgresSQL to achieve the expected result?

Advertisement

Answer

After further investigation I came up with the following solution:

DB-Fiddle


As comparison here is the MariaDB of the query:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement