SQL query output results from one table into two columns-CodePudding

I have a set of data which I have queried and have extracted the right data, but would like the output to be in two different columns. The query is

(
    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)
)

What I would like to achieve is to have the data in a format similar to this:

Month	Rain	Snow
1	23	0
2	12	34
3	23	9

The data is now shown under one column instead.

Month	Rain
1	34
1	9
1	23
2	12
3	23

CodePudding user response：

Use conditional aggregation:

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);

Depending on the actual RDBMS that you use, the above code could be further simplified.