Counting the number of rows with a value greater than or equal to a value from another column in SQL

Question

I have a table with two columns: a couple id and a number of "marks" for that couple. I'd like a result which lists the number of couples which have x marks or more for each of the values of x. So my input looks like:

| couple_id | num_marks |
|-----------+-----------|
|         9 |         7 |
|         6 |         6 |
|         8 |         6 |
|         2 |         5 |
|         3 |         4 |
|         5 |         4 |
|         1 |         3 |
|         4 |         3 |
|        10 |         2 |
|         7 |         1 |

And I'd like to get the result:

| num_marks | num_couples |
|-----------+-------------|
|         7 | 1           |
|         6 | 3           |
|         5 | 4           |
|         4 | 6           |
|         3 | 8           |
|         2 | 9           |
|         1 | 10          |

I.e. there was 1 couple with 7 or more marks, 3 couples with 6 or more marks, 4 couples with 5 or more marks, etc. I've been able to come up with a query to return the number of couples with exactly n marks:

SELECT num_marks,
       count(couple_id) AS num_couples
  FROM table_name
  GROUP BY num_marks
  ORDER BY num_marks DESC;

Which yields:

| num_marks | num_couples |
|-----------+-------------|
|         7 |           1 |
|         6 |           2 |
|         5 |           1 |
|         4 |           2 |
|         3 |           2 |
|         2 |           1 |
|         1 |           1 |

I.e. there was 1 couple with 7 marks, 2 couples with 6 marks, 1 with 5, etc. Is there a convenient way effectively to sum the value of each row with those above it? I can do it at the application level, but it seems like the kind of thing which really belongs in the database.

Answer 1

This might not be particularly efficient but should get the job done:

SELECT t1.num_marks,       
  (SELECT count(t2.couple_id)
   FROM table_name t2  
   WHERE t2.num_marks >= t1.num_marks
   ) AS num_couples 
FROM table_name t1  
GROUP BY t1.num_marks   
ORDER BY t1.num_marks DESC;

Edit : You can use a sub query in the select, from, where, group by and having clauses of a query, and if you reference the main / outer 'query' then it will evaluate the subquery for each row, then it is known as a correlated subquery. (Hence the caveat about performance)

As per Damien's answer, you could also use a CTE - CTE's can improve readability and also make recursion and self-joins a lot easier IMO.

AFAIK subqueries are supported in most SQL's.

Answer 2

You can use the RANK() function to work out where each result ranks, then just add the number of tied results onto that rank:

create table #T (couple_id int,num_marks int)
insert into #T (couple_id,num_marks)
select    9 ,         7 union all
select    6 ,         6 union all
select    8 ,         6 union all
select    2 ,         5 union all
select    3 ,         4 union all
select    5 ,         4 union all
select    1 ,         3 union all
select    4 ,         3 union all
select   10 ,         2 union all
select    7 ,         1

;with Ranked as (
    select num_marks,RANK() OVER (ORDER BY num_marks desc) as rk from #T
)
select num_marks,rk + COUNT(*) -1 as Result from Ranked
group by num_marks,rk

Gives:

num_marks   Result
----------- --------------------
7           1
6           3
5           4
4           6
3           8
2           9
1           10

(7 row(s) affected)

(Of course, if you need the results in a particular order, don't forget to add an ORDER BY clause - the above ordering is just a happy accident)