Multiple Y values

(4 posts) (2 voices)

Tags:

No tags yet.

  1. sd23c109, Member

    All,

    I am trying to create a Line chart that has time across the x axis, and several metric values for the y axis for a single object. I would like to have a chart that shows a line for points, a line for rebounds, another line for steals and another line for assists for a particular player for each month of the 2015 season. I have tried this query, but it is not working. It seems to group incorrectly:

    <<<<<<<<<<<<<<START SQL>>>>>>>>>>>>>>>>>>>>>>>>>

    DROP PROCEDURE IF EXISTS sp_DBR_PlayerStats
    $$
    CREATE PROCEDURE `sp_DBR_PlayerStats`(p_player varchar(30), inStartDate datetime, inEndDate datetime)
    BEGIN

    select 'dbr.title', 'Player Stats';
    select 'dbr.refresh', 50;
    SELECT 'dbr.chart', 'MSline', '';
    select 'dbr.chart.options', 'rotateNames', 45;
    select 'dbr.chart.options', 'axis', 'Time', 'Points', 'Rebounds', 'Steals', 'Assists';
    select 'dbr.chart.options', 'datetimescale', 1;
    select 'dbr.parameters.show';
    SELECT MONTH(GameDate) as 'Month',
    avg(Points) as 'Points',
    avg(Rebounds) as 'Rebounds',
    avg(Steals) as 'Steals',
    avg(Assists) as 'Assists'
    FROM playerstats
    WHERE playername like concat('%',p_player,'%')
    AND YEAR(GameDate)=2015
    GROUP BY GameDate;

    END
    $$

    <<<<<<<<<<<<<<<<Finish SQL>>>>>>>>>>>>>>>>>>>

    Can anyone assist?

  2. sd23c109, Member

    Ok, answered my own question. It requires UNIONs to get all of the lines on the same chart. You need to do a for each of the metrics, then union it with the next metric.

  3. myDBR Team, Key Master

    You can do it in one query. See below.

    Also, be careful with GROUP BY. You are grouping by the date, not by the month. As a rule of thumb, a GROUP by should always be all normal columns in the SELECT that do not contain the aggregate functions.

    SELECT
    MONTH(s.GameDate) as 'Month',
    c.type,
    case when c.type
    when 'Points' then avg(s.Points)
    when 'Rebounds' then avg(s.Rebounds)
    when 'Steals' then avg(s.Steals)
    when 'Assists' then avg(s.Assists)
    end as 'avg'
    FROM playerstats s
    join (
    select 'Points' as 'type'
    union
    select 'Rebounds'
    union
    select 'Steals'
    union
    select 'Assists'
    ) as c
    WHERE playername like concat('%',p_player,'%') AND YEAR(GameDate)=2015
    GROUP BY MONTH(s.GameDate), c.type;

    --
    myDBR Team

  4. sd23c109, Member

    That is a better way to do it than I had in mind. Thanks!


Reply

You must log in to post.