dbr.calc and Aggregation with summary level?

(6 posts) (3 voices)
  • Started 4 years ago by spyhunter88
  • Latest reply from myDBR Team

Tags:

No tags yet.

  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,

    Posted 4 years ago #
  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

    Posted 4 years ago #
  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,

    Posted 4 years ago #
  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

    Posted 4 years ago #
  5. nsepetys, Member

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

    Posted 1 year ago #
  6. myDBR Team, Key Master

    No, the solution would still be the same.

    --
    myDBR Team

    Posted 1 year ago #

Reply

You must log in to post.