Tsql aggregate group by results under specific threshold as "others"

user_182331 0
Viewed 166

i have an aggregate query which gives to me these results:

289         Andria - BT
97          N/A
97          Barletta - BT
47          Cerignola - FG
34          Corato - BA
33          Trani - BT
21          Bari - BA
19          Bitonto - BA
18          Bisceglie - BT
18          San Ferdinando di Puglia - BT
15          Foggia - FG
14          Molfetta - BA
14          Terlizzi - BA
12          Altamura - BA
9           San Severo - RA
9           Trinitapoli - BT
8           Margherita di Savoia - BT
7           Lucera - FG
6           Giovinazzo - BA
5           Capurso - BA
5           Minervino Murge - BT
5           Spinazzola - BT
5           Venosa - PZ
4           Stornara - FG
4           Milano - PG
4           Palo del Colle - BA

I would like to sum all values under some thresold (for example 10) in a single row; something like

289         Andria - BT
97          N/A
97          Barletta - BT
47          Cerignola - FG
34          Corato - BA
33          Trani - BT
21          Bari - BA
19          Bitonto - BA
18          Bisceglie - BT
18          San Ferdinando di Puglia - BT
15          Foggia - FG
14          Molfetta - BA
14          Terlizzi - BA
12          Altamura - BA
<SUM>       OTHER

The query is basically something like:

select count(ID) as Count, ISNULL(Description, 'N/A') as City
from 
Table    
group by Description

how can i achieve this result? Thanks

sql-server sql
3 Answers

Move the counting to a cte. Select its rows where count >= 10 and UNION ALL with the sum for rows having count < 10.

with cte as
(
    select count(ID) as Count, ISNULL(Description, 'N/A') as City
    from Table    
    group by Description
)
select * from cte where Count >= 10
UNION ALL
select SUM(count), 'Other' from cte where Count < 10
user_37060160

Try this one :

SELECT SUM(CountID),City
FROM(
       select count(ID) as CountID, 
              CASE WHEN count(ID) < 10 
                   THEN 'Other' 
                   ELSE ISNULL(Description, 'N/A')
              END as City

      from Table    
      group by Description
)m
Group BY City
user_49649230

It's little bit unclear to me, but I'll give you to queries, which are slightly different. Choose wichever satisfies you.

All you need is HAVING clause:

select count(ID) as Count, ISNULL(Description, 'N/A') as City
from 
Table    
group by Description
having count(ID) > 10

Or you can use this query:

select count(ID), City from (
    select ID, ISNULL(Description, 'N/A') as City
    from Table    
    where ID > 10
) as A
group by City
user_71325500
Related