Re: MySQL Rollup - All possible column super aggregations

[Rathi Rao]

Hi Peter,

Yes I have tried that. Please have a look at this query.

I inserted two more rows to the table in addition to the rows I had mentioned in my previous mail

insert into testrollup values (100,10,145,'abc',90);
insert into testrollup values (100,10,123,'abc',85);

Now, problem statement - With all the other aggregations, I want to get the sum(itemtypeid,districtid) for district = 'abc' and unitid = 10 which would be 150

The query goes like this

select
itemtypeid,unitid,dealerid,districtid,concat(itemtypeid,'-',districtid)itemdistrict,
sum(num)
from testrollup
where districtid = 'abc' and unitid =10
group by itemtypeid,unitid,dealerid,districtid,concat(itemtypeid,'-',districtid) with rollup;


I do get the rows which have the sum as 150. But the row looks like this

100,10,NULL,NULL,NULL,150
100,NULL,NULL,NULL,NULL,150

I do not get to see the row as NULL,NULL,NULL,NULL,100-abc,150

I observe that NULLs happen to start from right and proceeds to left. Is such a super aggregate row achievable or must the UNION approach be taken?NEF

Thanks
Rathi