I’m trying to count sum and count without group by with three different conditions and add written flag to distinguish them from each other. Then I’m trying to get the only one record with certain flag. But the result query from cte doesn’t filter as I expect. It works the same as if I add filter by flag_purchase to where clause in cte.

My query:

with cte as (
    select 
        'competitive' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cn
    from t15
    where 
        1=1
        and business_code = '1'
        and is_finished = '1'
    union all 
    select 
        'open' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cn
    from t15
    where 
        1=1
        and business_code = '2' and business_unit = '2'
        and is_finished = '1'

    union all 
    select 
        'closed' as flag_purchase
        , SUM(winner_price_wo_vat) as sum_wo
        , COUNT(prc_lot) as cn
    from t15
    where 
        1=1
        and business_code = '3' and business_unit = '1'
        and is_finished = '1'
)
select 
    flag_purchase
    , sum_wo
    , cnt
from cte 
where 
    1=1
    and flag_purchase = 'closed'

Output:

flag_purchase|sum_wo      |cnt|
-------------+------------+---+
open         |            |  0|
competitive  |            |  0|
closed       |237793562.37| 52|

If I’m adding group by in cte then it works as expected – output contains only one record where flag_purchase = ‘closed’.

Why does Clickhouse work like that and can I solve this problem only with group by?