drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dechang Gu <...@mapr.com>
Subject Re: How to avoid case sensitivity in group by
Date Thu, 09 Feb 2017 16:00:22 GMT
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.
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message