2 Replies Latest reply on Jun 18, 2006 4:17 PM by The ScareCrow

    charts

    JohnGree Level 1
      i have a chart which works unless i have a 0 value, i know i need to do something to my query but not sure what, i am using mysql as ny database.

      <cfquery name="getYear" datasource="Cricket">
      SELECT
      (Caught / TotalOuts) AS CaughtPer,
      (LBW / TotalOuts) AS LBWPer,
      (Bowled / TotalOuts) AS BowledPer,
      (Stumped / TotalOuts) AS StumpedPer
      FROM
      (SELECT
      SUM(BatHowOut <> 'Not Out') AS TotalOuts,
      SUM(BatHowOut = 'Caught') AS Caught,
      SUM(BatHowOut = 'LBW') AS LBW,
      SUM(BatHowOut = 'Bowled') AS Bowled,
      SUM(BatHowOut = 'Stumped') AS Stumped
      FROM
      BattingStats AA
      </cfquery>


      <cfchart chartwidth="250" backgroundcolor="747474" labelformat="percent" chartheight="140" show3d="yes" showlegend="no">
      <cfchartseries type="pie">
      <cfchartdata item="Caught" value="#GetYear.CaughtPer#">
      <cfchartdata item="Bowled" value="#GetYear.BowledPer#">
      <cfchartdata item="LBW" value="#GetYear.LBWPer#">
      <cfchartdata item="Stumped" value="#GetYear.StumpedPer#">
      </cfchartseries>
      </cfchart>
        • 1. charts
          IndigoZ
          Don't do straight division in SQL like that if TotalOuts is ever returning Zero, you are going to get a division by Zero Error.

          If Data Integrity isnt crucial, you can massage the data by using a CASE statement for Zero to turn any into a 1 when SUMing the values.

          Otherwise, Run this Query:

          <cfquery name="getSubYear" datasource="Cricket">
          SELECT
          SUM(BatHowOut <> 'Not Out') AS TotalOuts,
          SUM(BatHowOut = 'Caught') AS Caught,
          SUM(BatHowOut = 'LBW') AS LBW,
          SUM(BatHowOut = 'Bowled') AS Bowled,
          SUM(BatHowOut = 'Stumped') AS Stumped
          FROM
          BattingStats AA
          </cfquery>

          <!--- Then Check for Zero Values in a Query of Queries: --->

          <cfset checkIntegrity = getYear()>
          <cfif checkIntegrity.TotalOuts NEQ 0>
          <cfquery name="getYear" dbtype="query">
          SELECT
          (Caught / TotalOuts) AS CaughtPer,
          (LBW / TotalOuts) AS LBWPer,
          (Bowled / TotalOuts) AS BowledPer,
          (Stumped / TotalOuts) AS StumpedPer
          FROM
          getSubYear
          </cfquery>
          <cfelse>
          Custom Error Message
          </cfif>
          • 2. Re: charts
            The ScareCrow Level 1
            No need for the 2 queries, just do it in the query.

            SELECT
            (Caught / TotalOuts) AS CaughtPer,
            (LBW / TotalOuts) AS LBWPer,
            (Bowled / TotalOuts) AS BowledPer,
            (Stumped / TotalOuts) AS StumpedPer
            FROM
            (SELECT
            Case When SUM(BatHowOut <> 'Not Out') = 0 Then 1 Else SUM(BatHowOut <> 'Not Out') End AS TotalOuts,
            Case When SUM(BatHowOut = 'Caught') = 0 Then 1 Else SUM(BatHowOut = 'Caught') End AS Caught,
            Case When SUM(BatHowOut = 'LBW') = 0 Then 1 Else SUM(BatHowOut = 'LBW') End AS LBW,
            Case When SUM(BatHowOut = 'Bowled') = 0 Then 1 Else SUM(BatHowOut = 'Bowled') End AS Bowled,
            Case When SUM(BatHowOut = 'Stumped') = 0 Then 1 Else SUM(BatHowOut = 'Stumped') End AS Stumped


            I'm just not sure this is the correct syntax for mySQL, if you get an error you will need to check the manual

            Ken