You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Vineet Mishra <cl...@gmail.com> on 2015/06/17 10:51:14 UTC
Using ROLLUP to aggregate records
Hi All,
I am in middle of the requirement where I need to aggregate all the records
for ALL after group by, so it will be kind of ROLLUP operation where there
will be additional/one more row with addition of all the columns as
aggregated.
So for example, I am currently getting the records as
fm,to,obtd
vs,1,5
o,2,6
d,3,7
fv,4,8
on my query,
SELECT
ft.fm
,count(1) as to
,SUM(ft.lmtdf) as obtd
FROM STAGE.fsc as ft
where ft.sodp IS NOT NULL
group by ft.fm
where as what I am looking for is,
fm,to,obtd
vs,1,5
o,2,6
d,3,7
fv,4,8
ALL,10,26
So here ALL will be sum of all the records which is basically the ROLLUP
operation.
Urgent Call! Any quick help would be highly appreciated.
Thanks!
Re: Using ROLLUP to aggregate records
Posted by Vineet Mishra <cl...@gmail.com>.
Hi Hongbin,
It won't be possible for us to fire two queries since we have requirement
to publish dashboards on Tableau and making multiple queries through
Tableau is not possible.
It will be great if you could suggest me some other alternative for that.
Thanks,
On Wed, Jun 17, 2015 at 2:39 PM, hongbin ma <ma...@apache.org> wrote:
> can you use two separate SQLs to accomplish this?
>
>
> On Wed, Jun 17, 2015 at 4:51 PM, Vineet Mishra <cl...@gmail.com>
> wrote:
>
> > Hi All,
> >
> > I am in middle of the requirement where I need to aggregate all the
> records
> > for ALL after group by, so it will be kind of ROLLUP operation where
> there
> > will be additional/one more row with addition of all the columns as
> > aggregated.
> >
> > So for example, I am currently getting the records as
> >
> > fm,to,obtd
> > vs,1,5
> > o,2,6
> > d,3,7
> > fv,4,8
> >
> > on my query,
> >
> > SELECT
> > ft.fm
> > ,count(1) as to
> > ,SUM(ft.lmtdf) as obtd
> > FROM STAGE.fsc as ft
> > where ft.sodp IS NOT NULL
> > group by ft.fm
> >
> > where as what I am looking for is,
> >
> > fm,to,obtd
> > vs,1,5
> > o,2,6
> > d,3,7
> > fv,4,8
> > ALL,10,26
> >
> > So here ALL will be sum of all the records which is basically the ROLLUP
> > operation.
> >
> > Urgent Call! Any quick help would be highly appreciated.
> >
> > Thanks!
> >
>
>
>
> --
> Regards,
>
> *Bin Mahone | 马洪宾*
> Apache Kylin: http://kylin.io
> Github: https://github.com/binmahone
>
Re: Using ROLLUP to aggregate records
Posted by hongbin ma <ma...@apache.org>.
can you use two separate SQLs to accomplish this?
On Wed, Jun 17, 2015 at 4:51 PM, Vineet Mishra <cl...@gmail.com>
wrote:
> Hi All,
>
> I am in middle of the requirement where I need to aggregate all the records
> for ALL after group by, so it will be kind of ROLLUP operation where there
> will be additional/one more row with addition of all the columns as
> aggregated.
>
> So for example, I am currently getting the records as
>
> fm,to,obtd
> vs,1,5
> o,2,6
> d,3,7
> fv,4,8
>
> on my query,
>
> SELECT
> ft.fm
> ,count(1) as to
> ,SUM(ft.lmtdf) as obtd
> FROM STAGE.fsc as ft
> where ft.sodp IS NOT NULL
> group by ft.fm
>
> where as what I am looking for is,
>
> fm,to,obtd
> vs,1,5
> o,2,6
> d,3,7
> fv,4,8
> ALL,10,26
>
> So here ALL will be sum of all the records which is basically the ROLLUP
> operation.
>
> Urgent Call! Any quick help would be highly appreciated.
>
> Thanks!
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: Using ROLLUP to aggregate records
Posted by "Shi, Shaofeng" <sh...@ebay.com>.
Hi Vineet,
The “union” statement doesn’t work? If the clause before union can return
the result by each ft.fm, the clause after union can return the ALL
aggregated result, union them should return the result that you expected;
I might be wrong but please specify what’s the error with it; thanks;
On 6/17/15, 6:03 PM, "Vineet Mishra" <cl...@gmail.com> wrote:
>Hi Shi,
>
>I could see that the given query is not working as per the expectation.
>
>For the given query I could get only single record with the aggregated
>query as you mentioned above.
>
>It will be great if you could assist me with the same.
>
>Thanks,
>
>On Wed, Jun 17, 2015 at 2:39 PM, Shi, Shaofeng <sh...@ebay.com> wrote:
>
>> Kylin doesn¹t support this; But you can achieve this with some other
>>ways,
>> like aggregate once more in your app, or send a union query to Kylin,
>>like
>>
>> SELECT
>> ft.fm
>> ,count(1) as to
>> ,SUM(ft.lmtdf) as obtd
>> FROM STAGE.fsc as ft
>> where ft.sodp IS NOT NULL
>> group by ft.fm
>>
>>
>> Union
>>
>> SELECT
>> ³ALL² as fm
>> ,count(1) as to
>> ,SUM(ft.lmtdf) as obtd
>> FROM STAGE.fsc as ft
>> where ft.sodp IS NOT NULL
>>
>>
>>
>>
>> On 6/17/15, 4:51 PM, "Vineet Mishra" <cl...@gmail.com> wrote:
>>
>> >Hi All,
>> >
>> >I am in middle of the requirement where I need to aggregate all the
>> >records
>> >for ALL after group by, so it will be kind of ROLLUP operation where
>>there
>> >will be additional/one more row with addition of all the columns as
>> >aggregated.
>> >
>> >So for example, I am currently getting the records as
>> >
>> >fm,to,obtd
>> >vs,1,5
>> >o,2,6
>> >d,3,7
>> >fv,4,8
>> >
>> >on my query,
>> >
>> >SELECT
>> >ft.fm
>> >,count(1) as to
>> >,SUM(ft.lmtdf) as obtd
>> >FROM STAGE.fsc as ft
>> >where ft.sodp IS NOT NULL
>> >group by ft.fm
>> >
>> >where as what I am looking for is,
>> >
>> >fm,to,obtd
>> >vs,1,5
>> >o,2,6
>> >d,3,7
>> >fv,4,8
>> >ALL,10,26
>> >
>> >So here ALL will be sum of all the records which is basically the
>>ROLLUP
>> >operation.
>> >
>> >Urgent Call! Any quick help would be highly appreciated.
>> >
>> >Thanks!
>>
>>
Re: Using ROLLUP to aggregate records
Posted by Vineet Mishra <cl...@gmail.com>.
Hi Shi,
I could see that the given query is not working as per the expectation.
For the given query I could get only single record with the aggregated
query as you mentioned above.
It will be great if you could assist me with the same.
Thanks,
On Wed, Jun 17, 2015 at 2:39 PM, Shi, Shaofeng <sh...@ebay.com> wrote:
> Kylin doesn¹t support this; But you can achieve this with some other ways,
> like aggregate once more in your app, or send a union query to Kylin, like
>
> SELECT
> ft.fm
> ,count(1) as to
> ,SUM(ft.lmtdf) as obtd
> FROM STAGE.fsc as ft
> where ft.sodp IS NOT NULL
> group by ft.fm
>
>
> Union
>
> SELECT
> ³ALL² as fm
> ,count(1) as to
> ,SUM(ft.lmtdf) as obtd
> FROM STAGE.fsc as ft
> where ft.sodp IS NOT NULL
>
>
>
>
> On 6/17/15, 4:51 PM, "Vineet Mishra" <cl...@gmail.com> wrote:
>
> >Hi All,
> >
> >I am in middle of the requirement where I need to aggregate all the
> >records
> >for ALL after group by, so it will be kind of ROLLUP operation where there
> >will be additional/one more row with addition of all the columns as
> >aggregated.
> >
> >So for example, I am currently getting the records as
> >
> >fm,to,obtd
> >vs,1,5
> >o,2,6
> >d,3,7
> >fv,4,8
> >
> >on my query,
> >
> >SELECT
> >ft.fm
> >,count(1) as to
> >,SUM(ft.lmtdf) as obtd
> >FROM STAGE.fsc as ft
> >where ft.sodp IS NOT NULL
> >group by ft.fm
> >
> >where as what I am looking for is,
> >
> >fm,to,obtd
> >vs,1,5
> >o,2,6
> >d,3,7
> >fv,4,8
> >ALL,10,26
> >
> >So here ALL will be sum of all the records which is basically the ROLLUP
> >operation.
> >
> >Urgent Call! Any quick help would be highly appreciated.
> >
> >Thanks!
>
>
Re: Using ROLLUP to aggregate records
Posted by "Shi, Shaofeng" <sh...@ebay.com>.
Kylin doesn¹t support this; But you can achieve this with some other ways,
like aggregate once more in your app, or send a union query to Kylin, like
SELECT
ft.fm
,count(1) as to
,SUM(ft.lmtdf) as obtd
FROM STAGE.fsc as ft
where ft.sodp IS NOT NULL
group by ft.fm
Union
SELECT
³ALL² as fm
,count(1) as to
,SUM(ft.lmtdf) as obtd
FROM STAGE.fsc as ft
where ft.sodp IS NOT NULL
On 6/17/15, 4:51 PM, "Vineet Mishra" <cl...@gmail.com> wrote:
>Hi All,
>
>I am in middle of the requirement where I need to aggregate all the
>records
>for ALL after group by, so it will be kind of ROLLUP operation where there
>will be additional/one more row with addition of all the columns as
>aggregated.
>
>So for example, I am currently getting the records as
>
>fm,to,obtd
>vs,1,5
>o,2,6
>d,3,7
>fv,4,8
>
>on my query,
>
>SELECT
>ft.fm
>,count(1) as to
>,SUM(ft.lmtdf) as obtd
>FROM STAGE.fsc as ft
>where ft.sodp IS NOT NULL
>group by ft.fm
>
>where as what I am looking for is,
>
>fm,to,obtd
>vs,1,5
>o,2,6
>d,3,7
>fv,4,8
>ALL,10,26
>
>So here ALL will be sum of all the records which is basically the ROLLUP
>operation.
>
>Urgent Call! Any quick help would be highly appreciated.
>
>Thanks!