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

INSERT INTO region VALUES('Paris','23-01-15');
INSERT INTO region VALUES('Paris','28-01-15');
INSERT INTO region VALUES('France','23-01-16');
INSERT INTO region VALUES('France','22-07-17');
INSERT INTO region VALUES('France','21-01-17');
INSERT INTO region VALUES('Germany','25-01-18');

COMMIT;

I need a SELECT query where I would require DISTINCT region_name and based on the region_date. SELECT query should have 5 columns as region_name year_1 year_2 year_3 year_4 wherein year_1 is 2015 year_2 is 2016 and so on. Based on the region_name I need a COUNT for the region_date that how many regions fall in the same year. Below is my expected result

For Paris - for Year_1 - It has 2 records so the count is 2 for year_1 and for rest it is 0. Likewise, for all. I am wondering if this can be done.

Note: I have to restrict the report till the year 2018 only. If anything comes after 2018 then that will be included in the report

Tool used: SQL Developer(18c)

CodePudding user response：

Assuming a fixed 4 years in the result set, we can try the following pivot query:

SELECT
    region_name,
    COUNT(CASE WHEN EXTRACT(year FROM region_date) = 2015 THEN 1 END) AS Year_1,
    COUNT(CASE WHEN EXTRACT(year FROM region_date) = 2016 THEN 1 END) AS Year_2,
    COUNT(CASE WHEN EXTRACT(year FROM region_date) = 2017 THEN 1 END) AS Year_3,
    COUNT(CASE WHEN EXTRACT(year FROM region_date) = 2018 THEN 1 END) AS Year_4
GROUP BY
    region_name;

CodePudding user response：

You can use pivot to do so:

Schema and insert statements:

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

 INSERT INTO region VALUES('Paris','23-JAN-15');
 INSERT INTO region VALUES('Paris','28-JAN-15');

 INSERT INTO region VALUES('France','23-JAN-16');
 INSERT INTO region VALUES('France','22-JUL-17');
 INSERT INTO region VALUES('France','21-JAN-17');
 INSERT INTO region VALUES('Germany','25-JAN-18');

Query:

  select * from
  (SELECT region_name, extract(year from region_date)yr FROM region )
  pivot
  (
  COUNT(*) for yr  in (2015,2016,2017,2018)
  )

Output:

db<>fiddle here