sql (oracle) count and sum within the same select/where query

With reference to the following and correctly answered question:
sql (oracle) counting number of overlapping intervals

Given the following table test in an oracle sql database:

+----+------+-------+------+
| id | name | start | stop |
+----+------+-------+------+
| 1  |   A  |   1   |  5   |
+----+------+-------+------+
| 2  |   A  |   2   |  6   |
+----+------+-------+------+
| 3  |   A  |   5   |  8   |
+----+------+-------+------+
| 4  |   A  |   9   |  10  |
+----+------+-------+------+
| 5  |   B  |   3   |  6   |
+----+------+-------+------+
| 6  |   B  |   4   |  8   |
+----+------+-------+------+
| 7  |   B  |   1   |  2   |
+----+------+-------+------+

I would now like to find the number of overlapping intervals (endpoints included) [start, stop] n_overlap as well as the sum of the stop values for all id having the same name, i.e.:

+----+------+-------+------+-----------+------------+
| id | name | start | stop | n_overlap | sum_stops  |
+----+------+-------+------+-----------+------------+
| 1  |   A  |   1   |  5   |     3     |    19      |
+----+------+-------+------+-----------+------------+
| 2  |   A  |   2   |  6   |     3     |    19      |
+----+------+-------+------+-----------+------------+
| 3  |   A  |   4   |  8   |     3     |    19      |
+----+------+-------+------+-----------+------------+
| 4  |   A  |   9   |  10  |     1     |    10      |
+----+------+-------+------+-----------+------------+
| 5  |   B  |   3   |  6   |     2     |    14      |
+----+------+-------+------+-----------+------------+
| 6  |   B  |   4   |  8   |     2     |    14      |
+----+------+-------+------+-----------+------------+
| 7  |   B  |   1   |  2   |     1     |     2      |
+----+------+-------+------+-----------+------------+

I tried this solution, which works:

select t.*,
   (select count(*)
    from test t2
    where t2.name = t.name and
          t2.start <= t.stop and
          t2.stop >= t.start
   ) as n_overlap,
   (select sum(stop)
    from test t2
    where t2.name = t.name and
          t2.start <= t.stop and
          t2.stop >= t.start
   ) as sum_stops 
from test t;

But, is there a way to condense the two select/where queries, using e.g.:

select t.*,
   (select count(*) as n_overlap, sum(stop) as sum_stops
    from test t2
    where t2.name = t.name and
          t2.start <= t.stop and
          t2.stop >= t.start
   ) 
from test t;

which raises a too many values error?

Answer

You should be able to do what you want with a JOIN and GROUP BY:

SELECT t.id, t.name, t.start, t.stop, COUNT(t2.name) AS n_overlap, SUM(t2.stop) AS sum_stops
FROM test t
LEFT JOIN test t2 ON t2.name = t.name AND t2.start <= t.stop AND t2.stop >= t.start
GROUP BY t.id, t.name, t.start, t.stop

Output:

id  name    start   stop    n_overlap   sum_stops
1   A       1       5       3           19
2   A       2       6       3           19
3   A       5       8       3           19
4   A       9       10      1           10
5   B       3       6       2           14
6   B       4       8       2           14
7   B       1       2       1           2

Advertisement

Answer