Oracle 子選擇聯合多列

我想在一個位置選擇和統計所有的貓和狗；

location | cats | dogs |
new york | 150  | 66   |
paris    | 34   | 12   |

到目前為止我得到了什麼：

SELECT
   cats,dogs
FROM
   (select k.location, count(*) as cats from pet k, location lo where k.location= lo.id and k.pet= 'Cat' group by k.location)
   union
   (select k.location, count(*) as dogs from pet k, location lo where k.location= lo.id and k.pet= 'Dog' group by k.location)

但我只得到一列位置和貓……

然後試試這個：

select
 k.location,
 sum((case when k.pet = 'Cat' then 1 else 0 end)) as cats,
 sum((case when k.pet = 'Dog' then 1 else 0 end)) as dogs,
from pet k, location lo where k.location= lo.id and k.pet in ('Cat', 'Dog')
group by k.location
;

我不確定你需要什麼location lo，似乎你有所有必要的資訊pet k。

引用自：https://dba.stackexchange.com/questions/222533