group by 應用

Question

name   qty
A 5
A 10
B 15
B 20
c 25
D 30
D 35結果為
name  qty
A 15
B 60
D 65即分級時﹐將 B 與 C 算做一組
謝謝﹗﹗

Answer 1

--heheSELECT name,SUM(Qty) as Qty
FROM tb
GROUP BY CASE WHEN name='c' THEN 'B' ELSE name END

Answer 2

SELECT CASE 
           WHEN name IN('B','C') 
             THEN 'B' 
           ELSE name
         END AS name,
         SUM(qty) AS qty
FROM tb_name
GROUP BY CASE 
           WHEN name IN('B','C') 
             THEN 'B' 
           ELSE name
         END

Answer 3

先update 再select name,sum(qty) from tb group by name

Answer 4

select newname as name,sum(qty)
from (select case name when 'c' then 'b' else name end as newname,qty from tb) t
group by newname

Answer 5

set nocount on
declare @t table(name varchar(10),  qty int) 
insert @t select 'A' ,5 
insert @t select 'A', 10 
insert @t select 'B', 15 
insert @t select 'B', 20 
insert @t select 'c' ,25 
insert @t select 'D', 30 
insert @t select 'D', 35 
select replace(name,'C','B'),sum(qty) from @t group by replace(name,'C','B')
/*
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
A                                                                                                                                                                                                                                                                15
B                                                                                                                                                                                                                                                                60
D                                                                                                                                                                                                                                                                65*/

Answer 6

declare @t table
(
name char(1),
qty int
)insert @t select 'A',5
union all select 'A',10
union all select 'B',15
union all select 'B',20
union all select 'C',25
union all select 'D',30
union all select 'D',35select
a.name,
sum(a.qty) as qty
from(
select
name = case name when 'C' then 'B' else name end,
qty
from @t
)a
group by a.name

Answer 7

select name,qty=sum(qty) from
(select name=case name when 'c' then 'b' else name end,qty from tb) a
group by name