Want to summarize some fields and leave others alone SQL

Question

I have a query that results in a field of different lengths (6, 8, 10, ...) and corresponding calculations, as shown 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 summarize these results so that repeating lengths <18 are intact, but repeating lengths> = 18 are combined into one line, adding up the count field. Is it possible to do this in a single sql query without temporary tables and joining them?

Sorry if this is a simple question, I'm new to SQL.

Answer 1

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 2

. , :

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;

, , , , '1 Colname'

Answer 3

()

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. Use "999" to indicate the cumulative string.

Answer 4

Enter another highGroup field that is 0 if repeatlength <= 18, else 1, and then group on highGroupfield in an external query.