Search code examples
sqlsubquery

SQL Subselect calculated in one row

I have a trouble getting an SQL select to work.

I have one table - below is the example:

|id|name  |hits|
|1 |peter | 6  |
|2 |john  | 13 |
|3 |max   | 12 |
|4 |foo   | 8  |
|5 |bar   | 4  |

I want to calculate the sum of every entry under 10 hits and every entry over 10 hits in one output.

Like below:

|sum-over-10|sum-under-10|
| 25        | 18         |

I tried inner join and outer join but I think that's the wrong approach. If I do a subselect it always filters by the main select.

Don't know how to start here :-) Will be glad and thankful for any advice!

Solution

  • Just use conditional aggregation:

    select sum(case when hits < 10 then hits else 0 end),
       sum(case when hits > 10 then hits else 0 end)
from t;

    Note: The results do not include rows where hits = 10. You might want <= or >= if you want to include these.