如何获取每个不同类别的总计数

我想得到位置的非重复行,并得到特定宗族的玩家总数和明星玩家总数。更多解释请参见下文。

table p:
player_id    stars
1            10
2            10
3            10
4            10

table a:
player_id    location   clan
1               egate   dragons
2               ngate   sabrelights
3               sgate   dragons
4               wgate   tigers
5               lgate   fireflies
6               lgate   fireflies
7               egate   dragons

这是我试过的

    SELECT DISTINCT (CASE SUBSTR (location, 1, 1)
              WHEN 'e' THEN 'East'
              WHEN 'n' THEN 'North'
              WHEN 's' THEN 'South'
              WHEN 'w' THEN 'West'
              WHEN 'l' THEN 'Limbo'
              ELSE null
          END) loc
        ,clan clans  
        , sum (case when a.player_id IS NOT NULL THEN 1 ELSE 0 END) Total
        ,sum (case when p.stars = '10' THEN 1 ELSE 0 END) stars                                                                                            
        FROM   a
             left outer join p on p.PLAYER_ID = a.player_id 
                   group by SUBSTR (location, 1, 1),a.clan, p.STARS
            ,clan
           order by loc, clans

实际结果(错误):

LOC     CLANS    TOTAL  STARS
East    dragons     1   0
East    dragons     1   1
South   dragons     1   1
Limbo   fireflies   2   0
North   sabrelights 1   1
West    tigers      1   1

我想要的(期望的输出):

LOC     CLANS   TOTAL  STARS    
East    dragons     2   1
South   dragons     1   1
Limbo   fireflies   2   0
North   sabrelights 1   1
West    tigers      1   1