dbr.calc and Aggregation with summary level?

(6 posts) (3 voices)


No tags yet.

  1. spyhunter88, Member


    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?


  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.


  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


You must log in to post.