关于group by 的排序的问题

[jsidiot]

我的sql语句如下
sql="select guest,sum(case sign=1 then 1 else 0 end ) as sign_1,sum(case sign=2 then 1 else 0 end ) from table where guest<>'' group by sign_1 "
我想得到这样的结果。但是数据库告诉我不行，说找不到sign_1字段，但是我实在是无法找到其他的好的办法了！请问有什么好的解决办法？
谢谢

[pengdali]

sql="select * from (select guest,sum(case sign=1 then 1 else 0 end ) as sign_1,sum(case sign=2 then 1 else 0 end ) from table where guest<>'' group by guest ) tem order by sign_1"

[CrazyFor]

sql="select sign_1,sum(sign_2) from (select guest,sum(case sign=1 then 1 else 0 end ) as sign_1,sum(case sign=2 then 1 else 0 end ) sign_2 from table where guest<>'' group by guest ) tem order by sign_1"

[jsidiot]

不好意思
还有一个问题
我在这个语句里面还有group by ，我这么写
order by sign_1 group by other_sign
为什么 说我不对啊。
谢谢

[dafu71]

group by other_sign order by sign_1

[jsidiot]

这样我试过乐不行阿

[zosky]

select guest,sum(case sign=1 then 1 else 0 end ) as sign_1,sum(case sign=2 then 1 else 0 end ) from table where guest<>'' group by sum(case sign=1 then 1 else 0 end )

[pengdali]

select * from (select guest,sum(case sign=1 then 1 else 0 end ) [sign_1],sum(case sign=2 then 1 else 0 end ) [sign_2] from [你的表] where guest<>'' group by guest ) tem order by sign_1