hello, about hsql statistics. table mytable date,uid,a,b,c -------------------- 03/13/13 185690475 0 1 1 03/13/13 187270278 0 1 0 03/13/13 185690475 1 1 0 03/13/13 186012530 1 0 1 03/13/13 180286243 0 1 0 03/13/13 185690475 1 1 0 03/13/13 186012530 0 1 0 03/13/13 183256782 1 0 0 03/14/13 185690475 0 0 1
I want to get one day,each user total count,count a=1 ,count b=1, count c=1 the out put should like: key,total, counta, countb, countc ----------------------- 03/13/13:185690475 3 2 3 1 03/13/13:187270278 1 0 1 0 03/13/13:186012530 2 1 1 1 03/13/13:180286243 1 0 1 0 03/13/13:183256782 1 1 0 0 03/14/13:185690475 1 0 0 1 the hsql i want is: select concat(date:uid),count(1),count(a=1),count(b=1),count(c=1) from mytable group by uid,date; but I have to write ugly and inefficiency hsql like : select concat(s1.date:s1.uid),s1.total,s2.ca,s3.cb,s4.cc from ( select date,uid,count(1) total total from mytable group by uid,date) s1 inner outer join (select date,uid,count(1) ca total from mytable where a=1 group by uid,date)s2 inner outer join (select date,uid,count(1) cb total from mytable where b=1 group by uid,date)s3 inner outer join (select date,uid,count(1) cc total from mytable where c=1 group by uid,date)s4 ); each select sub-clause should run a map-reduce. if I have to count a very big number of columns table, this should be a very long task. some one have any good ideals? Thank you! Best Regards, Andy Zhou
