SQL查询将结果从一个表输出到两列中

Question

我有一组数据，我已经查询并提取了正确的数据，但希望输出在两个不同的列中。查询是

(
select month(observation_date) as month, count(total_snowfall_in) as snow 
from weather_table 
where city = 'Olympia' and year(observation_date) = 2019 and not (total_snowfall_in=0) 
group by month(observation_date) order by month(observation_date)
) union all
(
select month(observation_date) as month,  count(total_precip_in) as rain 
from weather_table 
where city = 'Olympia' and year(observation_date) = 2019 and not (total_precip_in=0) 
group by month(observation_date) 
order by month(observation_date)
)

我想实现的是以类似于以下格式的数据：

	月	雨	雪
1	23	0
2	12	34
3	23	9

使用条件聚合：

SELECT  MONTH(observation_date) AS month, 
COUNT(CASE WHEN total_snowfall_in <> 0 THEN 1 END) AS snow,
COUNT(CASE WHEN total_precip_in <> 0 THEN 1 END) AS rain  
FROM weather_table 
WHERE city = 'Olympia' AND YEAR(observation_date) = 2019 
GROUP BY MONTH(observation_date) 
ORDER BY MONTH(observation_date);

根据您使用的实际RDBMS，上面的代码可以进一步简化。