Count of Value column with case when and group by

user_13016237 0
Viewed 68

Table:

Date            Id       Value                    
2019-02-09      a1       ab            
2019-01-12      a2       cd
2019-12-12      a1       ab
2017-07-23      a1       ab
2018-12-09      a1       ab
2018-12-28      a1       cd
2016-11-09      a2       cd
2016-05-19      a2       ab

Output:

      Id        Max-Min               
      a1        1     
      a2       -1

The aim is to create Max_year_count-Min_year_count per Id.
For example, calculation in Value column :
(count of occurrence of value in max_year with group by Id)-(count of occurrence of value in min_year with group by Id)
Thanks !!

oracle-sqldeveloper oracle sql
2 Answers

Oracle supports FIRST/LAST for aggregates: 

SELECT id,
    -- latest year's count
    Count(*) KEEP (Dense_Rank LAST  ORDER BY Extract(YEAR From "Date"))
     -- oldest year's count
  - Count(*) KEEP (Dense_Rank FIRST ORDER BY Extract(YEAR From "Date"))
FROM DATA
GROUP BY Id
user_25279050

You can create a derived table of the maximum and minimum years for each Id value, and then count the number of years for that id value which match those values and subtract them to get Max-Min:

SELECT d.Id,
       COUNT(CASE WHEN EXTRACT(YEAR FROM "Date") = Max_Year THEN 1 END) -
       COUNT(CASE WHEN EXTRACT(YEAR FROM "Date") = Min_Year THEN 1 END) AS "Max-Min"
FROM data d
JOIN (SELECT Id, 
             MAX(EXTRACT(YEAR FROM "Date")) AS Max_Year,
             MIN(EXTRACT(YEAR FROM "Date")) AS Min_Year
      FROM data
      GROUP BY Id) m ON m.Id = d.Id
GROUP BY d.Id

Output:

ID  Max-Min
a1  1
a2  -1

Demo on dbfiddle

user_94737640
Related