表room id roomstru stastus
1 户型1 未售
2 户型2 已售
3 户型3 已售
4 户型1 已售
5 户型5 未售
6 户型3 未售
7 户型1 未售
8 户型1 已售
9 户型1 未售
10 户型4 已售
11 户型5 未售
12 户型4 已售 请问怎样统计出每种户型未售、已售状态的个数?谢谢!
总户数 已售 未售 销售比例
户型1
户型2
户型3
````
户型n
1 户型1 未售
2 户型2 已售
3 户型3 已售
4 户型1 已售
5 户型5 未售
6 户型3 未售
7 户型1 未售
8 户型1 已售
9 户型1 未售
10 户型4 已售
11 户型5 未售
12 户型4 已售 请问怎样统计出每种户型未售、已售状态的个数?谢谢!
总户数 已售 未售 销售比例
户型1
户型2
户型3
````
户型n
(
select roomstru,
count(*) 总户数,
sum(case stastus when '已售' then 1 else 0 end) 已售,
sum(case stastus when '未售' then 1 else 0 end) 未售
from room
group by roomstru
) t
count(case when status='已售' then 1 else null end)*0.1/count(*) 销售比例
from room group by roomstru或者
select roomstru, count(*) 总户数, sum(case when status='已售' then 1 else 0 end) 已售, sum(case when status='未售' then 1 else 0 end) 未售,
sum(case when status='已售' then 1 else 0 end)*0.1/count(*) 销售比例
from room group by roomstru
insert into tb values(1 , '户型1' , '未售')
insert into tb values(2 , '户型2' , '已售')
insert into tb values(3 , '户型3' , '已售 ')
insert into tb values(4 , '户型1' , '已售 ')
insert into tb values(5 , '户型5' , '未售 ')
insert into tb values(6 , '户型3' , '未售 ')
insert into tb values(7 , '户型1' , '未售 ')
insert into tb values(8 , '户型1' , '已售 ')
insert into tb values(9 , '户型1' , '未售 ')
insert into tb values(10, '户型4' , '已售 ')
insert into tb values(11, '户型5' , '未售 ')
insert into tb values(12, '户型4' , '已售 ')
goselect * , 已售*1.0/总户数 销售比例 from
(
select roomstru,
count(*) 总户数,
sum(case stastus when '已售' then 1 else 0 end) 已售,
sum(case stastus when '未售' then 1 else 0 end) 未售
from tb
group by roomstru
) t
drop table tb/*
roomstru 总户数 已售 未售 销售比例
---------- ----------- ----------- ----------- --------------------------
户型1 5 2 3 .400000000000
户型2 1 1 0 1.000000000000
户型3 2 1 1 .500000000000
户型4 2 2 0 1.000000000000
户型5 2 0 2 .000000000000(所影响的行数为 5 行)
*/
select a.roomstru,a.cnt as '總戶數',
isnull(b.cnt,0) as '已售',a.cnt-isnull(b.cnt,0) as '未售',
isnull(b.cnt,0)*1.0/a.cnt as '銷售比例'
from
(
SELECT roomstru,COUNT(*) as cnt
FROM room GROUP BY roomstru
)
a
left join
(
select roomstru,count(*) as cnt
FROM room WHERE status='已售' GROUP BY roomstru
)
b
on a.roomstru=b.roomstru
declare @t table(id int,roomstru varchar(20),stastus varchar(20))
insert into @t select 1,'户型1','未售'
insert into @t select 2,'户型2','已售'
insert into @t select 3,'户型3','已售'
insert into @t select 4,'户型1','已售'
insert into @t select 5 ,'户型5','未售'
insert into @t select 6 ,'户型3','未售'
insert into @t select 7,'户型1','未售'
insert into @t select 8,'户型1','已售'
insert into @t select 9,'户型1','未售'
insert into @t select 10,'户型4','已售'
insert into @t select 11,'户型5','未售'
insert into @t select 12,'户型4','已售' select roomstru [ ],总户数,已售,未售,cast(cast(已售*1.0*100/总户数 as numeric(5,2)) as varchar)+'%' [销售比例]
from
(select roomstru,count(1) [总户数],sum(case stastus when '已售' then 1 else 0 end)[已售],
sum(case stastus when '未售' then 1 else 0 end)[未售]
from @t group by roomstru)a