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

user_6228056 0
Viewed 62

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
google-bigquery sql
3 Answers

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;
user_18632290

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.

user_116950490

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

enter image description here

user_52219440
Related