mardi 6 décembre 2016

mysql: nested if summing

I want to count how many times confirmed_at was not null given that a promotion_id is either 1483 or 1887.

select
    IF(promotion_id IN(1483,1887), sum(IF(confirmed_at IS NOT NULL,1,0)),0) as all_us_vs_voda,
    status_inv
from blabla

group by status_inv

Example:

promotion_id status_inv confirmed_at
1            'before'    2016-05
1483         'before'    NULL
1483         'after'     2016-05
1483         'after'     2016-07
1887         'before'    2016-08
1887         'before'    2017-09
1887         'after'    2017-09

So, the result would be:

status_inv  not_nul_specific_promotions
before      2
after       3 

Aucun commentaire:

Enregistrer un commentaire