Sql Server group by , count query

Question

I have the following query

select COUNT(mc_owner) as nbr , mc_owner
from [dbo].[full] 
where (date_reception > '01-01-2015')
group by (mc_owner)
order by nbr desc

this shows me

• 20000 element1
• 10000 element2
• 10000 element3
• 10000 element4

i need to have only two results element1 and others grouped in on element something like

• element1 20000
• others 30000

Thank you for helping

Answer 1

declare   @tt table(name varchar(10),id int)

insert into @tt(name,id)values ('element1',2000)
insert into @tt(name,id)values ('element2',2000)
insert into @tt(name,id)values ('element3',2000)
insert into @tt(name,id)values ('element4',2000)


;WITH CTE AS
(
select ROW_NUMBER()OVER(Partition by ID ORDER BY ID,name) RN, * from @tt 

)
select NAME,ID from CTE
where RN = 1
UNION ALL
select 'Others' as NAME,SUM(id) as ID 
from CTE
where RN >1

Answer 2

You do it with a CASE expression, like this:

SELECT
    COUNT(*) as nbr,
    CASE mc_owner WHEN 'Element1' THEN 'Element1' ELSE `Others` END as Owner
FROM [dbo].[full] 
WHERE (date_reception > '01-01-2015')
GROUP BY CASE mc_owner WHEN 'Element1' THEN 'Element1' ELSE `Others` END
ORDER BY COUNT(*) DESC

The idea is to provide an expression that evaluates to one of two values that you need - i.e. 'Element1' or Others, depending on the value of the mc_owner field.