1. 

   spyhunter88, Member

   Hi,

   I have an issue about using dbr.calc with Col.sum reference.

   
   
   select 'dbr.dbr', 'Header';
   
   select 'dbr.sum', 'Value';
   
   select 'dbr.calc', 'percent', '[Value]/[Value.sum]*100';
   
   select Header, Name, Value as '[Value]', null as 'percent' from Table order by Header;
   
   

   This will divide each item with Total Value in the report, that's not what I want. I want the ratio in each Header group.

   How can I do it?

   Thanks,
2. 

   myDBR Team, Key Master

   The Value.sum refers to the full sum. What you can do is to it in a query:

   select 'dbr.hdr', 'Header';
   
   select 'dbr.sum', 'Value';
   
   select 'dbr.max', 'svalue';
   
   select t.Header, t.Name, t.Value, t.percent, s.svalue
   
   from Table t
   
     join (
   
       select Header, sum(Value) as 'svalue'
   
       from Table
   
       group by Header
   
     ) s on s.Header=t.Header;

   --
   myDBR Team
3. 

   spyhunter88, Member

   Nice, but the Table is not static, it's temporary table so I can not re-use (in mysql). So I must re-call all Select code. But I'll take this solution for now.

   Thanks,
4. 

   myDBR Team, Key Master

   If you have the data in a temporary table, MySQL does not allow to refer the same table more than once in the same query. Instead of re-calling the original select code, you can run the aggregate query into another temporaty table. Will be much faster and easier.

   --
   myDBR Team
5. 

   nsepetys, Member

   There has not been a new command to handle the hdr sum situation spyhunter88 mentioned above, has there?
6. 

   myDBR Team, Key Master

   No, the solution would still be the same.

   --
   myDBR Team

Reply

You must log in to post.