How to consecutively count everything greater than or equal to itself in SQL?-CodePudding

Let's say if I have a table that contains Equipment IDs of equipments for each Equipment Type and Equipment Age, how can I do a Count Distinct of Equipment IDs that have at least that Equipment Age.

For example, let's say this is all the data we have:

equipment_type	equipment_id	equipment_age
Screwdriver	A123	1
Screwdriver	A234	2
Screwdriver	A345	2
Screwdriver	A456	2
Screwdriver	A567	3

I would like the output to be:

equipment_type	equipment_age	count_of_equipment_at_least_this_age
Screwdriver	1	5
Screwdriver	2	4
Screwdriver	3	1

Reason is there are 5 screwdrivers that are at least 1 day old, 4 screwdrivers at least 2 days old and only 1 screwdriver at least 3 days old.

So far I was only able to do count of equipments that falls within each equipment_age (like this query shown below), but not "at least that equipment_age".

SELECT
equipment_type,
equipment_age,
COUNT(DISTINCT equipment_id) as count_of_equipments
FROM equipment_table
GROUP BY 1, 2

CodePudding user response：

Consider below join-less solution

select distinct
  equipment_type,
  equipment_age,
  count(*) over equipment_at_least_this_age as count_of_equipment_at_least_this_age
from equipment_table 
window equipment_at_least_this_age as (
  partition by equipment_type 
  order by equipment_age 
  range between current row and unbounded following
)

if applied to sample data in your question - output is

CodePudding user response：

Use a self join approach:

SELECT
    e1.equipment_type,
    e1.equipment_age,
    COUNT(*) AS count_of_equipments
FROM equipment_table e1
INNER JOIN equipment_table e2
    ON e2.equipment_type = e1.equipment_type AND
       e2.equipment_age >= e1.equipment_age
GROUP BY 1, 2
ORDER BY 1, 2;

CodePudding user response：

GROUP BY restricts the scope of COUNT to the rows in the group, i.e. it will not let you reach other rows (rows with equipment_age greater than that of the current group). So you need a subquery or windowing functions to get those. One way:

SELECT
   equipment_type,
   equipment_age,
   (Select COUNT(*) 
    from equipment_table cnt 
     where cnt.equipment_type = a.equipment_type
       AND cnt.equipment_age >= a.equipment_age
     )  as count_of_equipments
FROM equipment_table a
GROUP BY 1, 2, 3

I am not sure if your environment supports this syntax, though. If not, let us know we will find another way.