SQL query output results from one table into two columns

Question

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

Please specify the output that you get now

cooleck
– cooleck

2022-11-05 18:06:32 +00:00
Commented Nov 5, 2022 at 18:06 — cooleck
– cooleck, Commented Nov 5, 2022 at 18:06
@cooleck updated the answer above.

Grendizer
– Grendizer

2022-11-05 18:14:34 +00:00
Commented Nov 5, 2022 at 18:14 — Grendizer
– Grendizer, Commented Nov 5, 2022 at 18:14

forpas · Accepted Answer · 2022-11-05 18:39:25Z

2

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.

answered Nov 5, 2022 at 18:39

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query output results from one table into two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related