How to PIVOT columns to display the count and fetch last 3 years records based on the current year-CodePudding

CREATE TABLE region (
    region_name  VARCHAR2(255),
    region_date  DATE
);

INSERT INTO region VALUES('Paris','23-01-19');
INSERT INTO region VALUES('Paris','28-01-19');
INSERT INTO region VALUES('France','23-01-20');
INSERT INTO region VALUES('France','22-07-21');
INSERT INTO region VALUES('France','21-01-21');
INSERT INTO region VALUES('Germany','25-01-22');

COMMIT;

I need a SELECT query where I would require DISTINCT region_name and based on the region_date. SELECT query should have 4 columns as region_name year_1 year_2 year_3 wherein year_1, year_2, and year_3 column header is static but the values will be dynamic. Based on the region_name I need a COUNT for the region_date that how many regions fall in the same year. The only challenge is that the result should contain the data only for the last 3 years from the current year. Suppose in my sample data I need to display the COUNT only for those ones that are less than 3 years from the current date i.e till 2020. So, according to the sample data, it should display results as shown below:

 ------------- -------- -------- -------- 
| REGION_NAME | Year_1 | Year_2 | Year_3 |
 ------------- -------- -------- -------- 
| Paris       |      2 |      0 |      0 |
| France      |      0 |      1 |      2 |
| Germany     |      0 |      0 |      0 |
 ------------- -------- -------- --------

Let's assume we have are in the year 2023 then, in that case, it should give me the last 3 years' data i.e till 2021.

CodePudding user response：

You can use conditional aggregation such as

SELECT region_name,
       SUM(CASE
             WHEN TO_CHAR(region_date, 'yyyy') = TO_CHAR(sysdate, 'yyyy') - 2 THEN
              1
             ELSE
              0
           END) AS Year_1,
       SUM(CASE
             WHEN TO_CHAR(region_date, 'yyyy') = TO_CHAR(sysdate, 'yyyy') - 1 THEN
              1
             ELSE
              0
           END) AS Year_2,
       SUM(CASE
             WHEN TO_CHAR(region_date, 'yyyy') = TO_CHAR(sysdate, 'yyyy') THEN
              1
             ELSE
              0
           END) AS Year_3
  FROM region
 GROUP BY region_name