group by multiple columns and sum

Question

I have the following query

select id, c as country, 
        if(reason in [11, 100], 'food',  dictGetString('data.lables', 'threat_group', tuple(toInt16(reason)))) as group,
        toInt32(sumIf(count, dictGetString('data.lables',   'group', tuple(toInt16(reason))) IN
        ('Invalid a', 'Invalid b')
      AND reason NOT IN (200, 300) 
      AND url_path NOT iLIKE '%hello%'
    ) as invalid 
from data.table 
where id in(5957) 
group by id, group,country 

I get the following results

id   country    group       invalid
100     US      Not Known   30
100     GB      Known       2
100     GB      Undeclared  3
100     UA                  0
100     US      Known       20
100     GB                  2
100     TR      Undeclared   0
100     UA      Undeclared   3

I need to group by two columns country and group separately for invalid so I have a result like this

id     country      group      invalid
100     -           Not Known  38
100     -           Known      22
100     -           Undeclared  3
100     US          -          50
100     GB          -           7
100     UA          -           3



    

Answer 1

You can use your query in a WITH clause to use your output data as temp table:

    with cte as (
           select id, c as country, 
                        if(reason in [11, 100], 'food',  dictGetString('data.lables', 'threat_group', tuple(toInt16(reason)))) as group,
                        toInt32(sumIf(count, dictGetString('data.lables',   'group', tuple(toInt16(reason))) IN
                        ('Invalid a', 'Invalid b')
                      AND reason NOT IN (200, 300) 
                      AND url_path NOT iLIKE '%hello%'
                    ) as invalid 
                from data.table 
                where id in(5957) 
                group by id, group,country 
        )
    select min(id), '-' as country, group_, sum(invalid)
    from cte
    where group_ is not null
    group by group_
    UNION
    select min(id), country, '-' as group_, sum(invalid)
    from cte
    where invalid > 0
    group by country