Want to sum some fields and leave others alone SQL

Question

I have a query that results in a field of varying lengths (6, 8, 10, ...) and respective counts as seen below:

"region","repeatLength","count"
"promoter","6","272387"
"promoter","8","86929"
"promoter","10","28337"
"promoter","12","8873"
"promoter","14","3080"
"promoter","16","1098"
"promoter","18","475"
"promoter","20","206"
"promoter","22","133"
"promoter","24","75"
"promoter","26","42"
"promoter","28","32"
"promoter","30","16"
"promoter","32","6"
"promoter","34","9"

This table was generated by this call:

select region, repeatLength, count(*) as count 
from alignedRepeats
group by region, repeatLength;

I would like to be able to condense these results so that repeat lengths < 18 are intact, but repeat lengths >= 18 are aggregated into one row by summing the count field. Is this possible to do in a single sql query without making temporary tables and unioning them?

Sorry if this is a simple question, I am an SQL newbie.

Answer 1

Introduce another field, highGroup, which is 0 if repeatlength <= 18, else 1, then group on the highGroupfield in an outer query.

Answer 2

select region, 
    case when repeatLength >= 18 then ">=18" else repeatLength end as repeatLength, 
    count(*) as count  
from alignedRepeats 
group by region, 
    case when repeatLength >= 18 then ">=18" else repeatLength end; 

Answer 3

You shouldn't have to create a temporary table. You can use a union, though:

select region, repeatLength, 1 as Colname
from alignedRepeats
where repeatLength < 18
union
select region, repeatLength, count(*)
from alignedRepeats
where repeatLength >= 18
group by region, repeatLength;

Not sure what you want the last field to contain, though, hence '1 as Colname'

Answer 4

The correct answer (tested)

select
    region,
    repeatLength,
    count(*) as count
from
    alignedRepeats
where
    repeatLength < 18
group by
    region,
    repeatLength
union
select
    region,
    999,
    count(*)
from
    alignedRepeats
where
    repeatLength >= 18
group by
    region

Note: Using "999" to indicated the aggregate row.