Question on Chart and sum

(3 posts) (2 voices)

Tags:

  1. Selvi, Member

    I would like a chart to be the format of "Column". I want the chart to pick up the sum of the second column from the select statement for 'y' and the first column for 'x'. My select statements are as follows: (As the main query is complicated, I would like to avoid the "sum" function to be in the second query. I was wondering if the chart of myDBR can do this "sum" function similar to crosstab/reports in myDBR if possible.)

    Can I accomplish the following three steps in some form in myDBR?

    select 'dbr.chart', 'Column';
    select 'dbr.sum', 2;

    select DATE(h.some_date) as DATE1, max(h.field1)-min(h.field1) as SOME_NUMERIC from mydb.table1 h join mydb.table2 m on h.t1_id=12.id where DATE(h.some_date) >= '2009-06-13' and DATE(h.some_date) <= DATE_ADD('2009-06-13', INTERVAL 6 DAY) and t2.some_field='XYZ' group by t2.ID, DATE1 order by DATE1;

    My objective is to plot DATELL versus sum(max(h.field1)-min(h.field1)).
    Query explanation: Getting (max-min) value for field1 pertaining to each ID for different dates. As "some_field" is in a different table, I had to join on an ID field.

    Please post any suggestions as to if the aggregate functions can be carried out under "charts" similar to "reports".

    Thanks.

    Posted 9 years ago #
  2. myDBR Team, Key Master

    The 'dbr.sum' is for the table reports. Chart reports expect the data to be ready.

    If understood your query correctly you are looking something like this:

    select 'dbr.chart', 'Column';
    
    select date1 as 'DATE1', sum(minmax) as 'SOME_NUMERIC'
    from (
    select t2.id, DATE(h.some_date) as 'date1', max(h.field1)-min(h.field1) as 'minmax'
    from mydb.table1 h join mydb.table2 m on h.t1_id=12.id
    where DATE(h.some_date) >= '2009-06-13' and DATE(h.some_date) <= DATE_ADD('2009-06-13', INTERVAL 6 DAY) and t2.some_field='XYZ'
    group by t2.id, DATE(h.some_date)
    ) as q
    group by date1;

    Posted 9 years ago #
  3. Selvi, Member

    Thanks for your response/help!

    Posted 9 years ago #

Reply

You must log in to post.