遇到分组求和的有关问题

遇到分组求和的问题
建表脚本

求高手指点下，非常感谢                                SQL                  select                  脚本                  行业数据                  对象              
[解决办法]

select shelfid,count(distinct goodsid) 'goodsidcount'
 from testtb
 group by shelfid

/*
shelfid                                            goodsidcount
-------------------------------------------------- ------------
1                                                  3
2                                                  3

(2 row(s) affected)
*/

[解决办法]
select shelfid,COUNT(goodsidcount )goodsidcount 
FROM (
SELECT  shelfid,COUNT(goodsid) goodsidcount 
FROM testtb
GROUP BY shelfid,goodsid
HAVING COUNT(goodsid)>1)a
GROUP BY shelfid

------解决方案-------------------- 
 
select shelfid,count(1) from
(
select shelfid from  [testtb] group by shelfid,goodsid having(count(1)) >1
)a
group by shelfid
[解决办法]
SELECT  a.shelfid ,
        ISNULL(COUNT(goodsidcount), 0) goodsidcount
FROM    ( SELECT DISTINCT
                    shelfid
          FROM      testtb
        ) a
        LEFT JOIN ( SELECT  shelfid ,
                            COUNT(goodsid) goodsidcount
                    FROM    testtb
                    GROUP BY shelfid ,
                            goodsid
                    HAVING  COUNT(goodsid) > 1
                  ) b ON a.shelfid = b.shelfid
GROUP BY a.shelfid