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 <sanjivktr@gmail.com>
Sent: Wednesday, February 8, 2017 10:11:11 PM
To: user@drill.apache.org
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 <sanjivktr@gmail.com> 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 <sanjivktr@gmail.com> 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.
|