charts

[Guest]

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>

[Guest]

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>

[The_ScareCrow]

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