How about the following:
0: jdbc:drill:schema=dfs.tpchRaw> select lower(state), sum(price) from junk group by lower(state); +----------+---------+ | EXPR$0 | EXPR$1 | +----------+---------+ | alaska | 165 | | florida | 170 | | texas | 80 | +----------+---------+ 3 rows selected (4.259 seconds) 0: jdbc:drill:schema=dfs.tpchRaw> select upper(state), sum(price) from junk group by upper(state); +----------+---------+ | EXPR$0 | EXPR$1 | +----------+---------+ | ALASKA | 165 | | FLORIDA | 170 | | TEXAS | 80 | +----------+---------+ ________________________________ From: Sanjiv Kumar <[email protected]> Sent: Wednesday, February 8, 2017 10:11:11 PM To: [email protected] Subject: Re: How to avoid case sensitivity in group by Hello This is my input table in db:- State Price Alaska 75 Texas 80 Alaska 90 Florida 100 florida 70 Sql Server Query:- select State, Sum (Price) from testTable group by State. Sql Server Output:- State Price Alaska 165 Texas 80 Florida 170 Drill Query:- select T1.State, Sum(T1.Price) from <pluginName>.<testTable> T1 group by T1.State. Drill Output:- State Price Alaska 165 Texas 80 Florida 100 florida 70 I want same output as shown in Sql Server Output. Please help. On Wed, Feb 8, 2017 at 6:31 PM, Sanjiv Kumar <[email protected]> wrote: > lower and upper is not a solution which i am aspecting. I want to > display same output as i get through sql server management studio > > On Wed, Feb 8, 2017 at 5:15 PM, Sanjiv Kumar <[email protected]> wrote: > >> Drill Environment:- OS:- Window 10 >> Version:- 1.9 >> Mode:- embedded mode >> >> I have a column name 'State' in db, which have data like(e.g:- Florida, >> florida,new jersey, etc). >> >> My problem is while using count(State) and group by in query, >> Florida and florida are showing two seperate rows. >> >> So how to avoid case sensitivity so that both Florida and florida act >> as 1 rows while firing count(Sate).? >> >> >> -- >> Thanks & Regards. >> Sanjiv Kumar. >> > > > > -- > Thanks & Regards. > Sanjiv Kumar. > -- Thanks & Regards. Sanjiv Kumar.
