My above solution made an implicit assumption that we return null even if a single value in the column b is null. However you can modify the query to replace nulls with 0's if that is what you want to do.
On Tue, Apr 9, 2019 at 4:41 PM rahul challapalli <[email protected]> wrote: > I haven't tried it myself but something like the below workaround should > be helpful > > select > a, > case > when exists (select 1 from dfs.`sample.json` where b is null) then > null > else sum(b) > end > from dfs.`sample.json` > group by a > > - Rahul > > On Tue, Apr 9, 2019 at 4:32 PM Gayathri Selvaraj < > [email protected]> wrote: > >> Hi Team, >> >> >> Facing some issues with the following case: >> >> Json file (*sample.json*) is having the following content: >> {"a":2,"b":null} {"a":2,"b":null} {"a":3,"b":null} {"a":4,"b":null} >> >> *Query:* >> >> SELECT a, sum(b) FROM dfs.`C:\\Users\\user\\Desktop >> sample.json` group by a; >> >> *Error:* >> >> UNSUPPORTED_OPERATION ERROR: Only COUNT, MIN and MAX aggregate functions >> supported for VarChar type >> >> *Observation:* >> >> If we query without using group by, then it is working fine without any >> error. If group by is used, then sum of null values is throwing the above >> error. >> >> >> >> Can anyone please let us know the solution for this or if there are any >> alternative. I have raised a JIRA ticket for the same - >> https://issues.apache.org/jira/browse/DRILL-7161 >> >> >> Regards, >> >> Gayathri >> >
