sql group by case condition repeat in select

user_3875161 0
Viewed 430

sql:

select (case when order_id != 0 then order_id end) as order_id,
   (case when order_id = 0 then other_field end) as other_field,
   count(*)
from table t
group by (case when order_id != 0 then order_id end),
     (case when order_id = 0 then other_field end);

the case condition (case when order_id != 0 then order_id end) and (case when order_id = 0 then other_field end) must repeat in select query, and I have tried to add as field in group by condition like

select order_id,
   other_field,
   count(*)
from table t
group by (case when order_id != 0 then order_id end) as order_id,
     (case when order_id = 0 then other_field end) as order_field;

but it shows error, is it possible to use case condition once? because perhaps the condition will become too long to understand.

hive sql
1 Answers

its definitely possible and you have to repeat the case statements in this situation (or alias them in a subquery then refer to them by their alias to avoid repeating), I've done it many times. But 1) You cannot have aliases in the group by part 2) Your case statements seem syntactically wrong as they are missing the ELSE part.

Try like so:

select (case when order_id != 0 then order_id else NULL end) as order_id,
   (case when order_id = 0 then other_field ELSE NULL end) as other_field,
   count(*)
from table t
group by case when order_id != 0 then order_id else NULL end,
     case when order_id = 0 then other_field ELSE NULL end;

To avoid repetition you can do this:

     select order_id, other_field,
           count(*)
        from (
             select (case when order_id != 0 then order_id else NULL end) as order_id,
                    (case when order_id = 0 then other_field ELSE NULL end) as other_field     from table 
            )t
        group by order_id, other_field ;

Edit: As Gordon Linoff said, the else part is not required after all, so you could have just this:

   select order_id, other_field,
           count(*)
        from (
             select (case when order_id != 0 then order_id end) as order_id,
                    (case when order_id = 0 then other_field E end) as other_field     from table 
            )t
        group by order_id, other_field ;
user_29448950
Related