CODEWITHSUNDEEP
sqlimpala
aaaaaaaaaron_g
aaaaaaaaaron_g

Reputation: 95

Select multiple counts with different conditions and only count distinct, returning results in a single row

I have seen a lot of questions on how to select multiple counts in the same row where the solution is something like.

SELECT
    sum(case when rqbg = 'B' and rxbg = 'G' and rxmid is not null then 1 else 0 end) AS btg_changed,
    sum(case when rqbg = 'B' and rbg = 'G' and rxmid is not null then 1 else 0 end) AS btg_possible,
FROM fact 
where dt between '2021-04-01' and '2021-04-28'
and sender_account_id = 111111;

I want to do this but only sum up when a specific parameter, rmid, is distinct.

I can do this using UNION ALL, for example:


SELECT count(DISTINCT rmid) AS "btg_changed"
from fact
where rqbg = 'B' 
and rbg = 'G' 
and rxmid is not null 
and rxbg = 'G'

UNION ALL

SELECT count(DISTINCT rmid) AS "btg_possible"
from fact
where rqbg = 'B' 
and rbg = 'G' 
and rxmid is not null

The problem here is that this latter approach does not actually make each value its own column - every count is in the same column which.

So how can I do this where I only count distinct rows based on rmid but I am also getting each count in its own column. The final result should just be one row with a few different counts. I have a few other things I want to ask but only included the two selects for clarity.

I am using Impala.

I have also changed some of the column names to obfuscate what I am working with a little so apologies if it is confusing.

Upvotes: 0

Views: 611

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Just use conditions:

select count(case when rxbg = 'G' then rmid end) AS btg_changed,
       count(distinct rmid) as btg_possible
from fact
where rqbg = 'B' and rbg = 'G' and rxmid is not null

Upvotes: 1

Related Questions