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.