CODEWITHSUNDEEP
sqlhive
mamba
mamba

Reputation: 25

SQL/Hive How to combine two different queries into one result with different columns

Sorry if the title sucks. I would like to basically convert both of these queries into one query with the result having two columns:

select count(columnA) as prev from myTable where set_id = 1530880217;

select count(columnA) as curr from myTable where set_id = 1530901756;

Output:

prev | curr

1000 | 1500

Upvotes: 0

Views: 832

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation:

select sum(case when set_id = 1530880217 then 1 else 0 end) as prev,
       sum(case when set_id = 1530901756 then 1 else 0 end) as curr
from myTable
where set_id in (1530880217, 1530901756);

This assumes that columnA is never NULL. If you really want the NULL check:

select sum(case when set_id = 1530880217 and ColumnA is not null then 1 else 0 end) as prev,
       sum(case when set_id = 1530901756 and ColumnA is not null then 1 else 0 end) as curr
from myTable
where set_id in (1530880217, 1530901756);

Upvotes: 1

Related Questions