Toad World® Forums

Basic Count, Sum, and Division script getting error

Hi.

Using TAOD Data Point.

I am getting an error:"Incorrect syntax near “CAST” expected “AS”.

Here is the script:

SELECT
COUNT() AS TotalCount
,SUM(“TRUE Flag”) AS CountOfOnes
,(COUNT(
) - SUM(“TRUE Flag”)) AS CountOfZeroes
,((COUNT() - (CAST(SUM(“TRUE Flag”) AS FLOAT)) / COUNT()) * 100) AS FalsePercentage
,(((CAST(SUM(“TRUE Flag”)) AS FLOAT) / COUNT(*)) * 100) AS TruePercentage
FROM RiskAdjust_Temp.dbo.WHP_Mem_Lev_Conf1;

Not sure what is going on.

Other similar scripts like these seem to work fine.

SELECT
COUNT() AS TotalCount
,SUM(“TRUE Flag”) AS CountOfOnes
,COUNT(
) - SUM(“TRUE Flag”) AS CountOfZeroes
, (COUNT(*) - (CAST(SUM(“TRUE Flag”) AS FLOAT))) / (CAST(SUM(“TRUE Flag”) AS FLOAT)) * 100 AS FalsePercentage
FROM RiskAdjust_Temp.dbo.WHP_Mem_Lev_Conf1;

SELECT
COUNT() AS TotalCount
,SUM(“TRUE Flag”) AS CountOfOnes
,COUNT(
) - SUM(“TRUE Flag”) AS CountOfZeroes
, (COUNT() - (CAST(SUM(“TRUE Flag”) AS FLOAT))) / (CAST(SUM(“TRUE Flag”) AS FLOAT)) * 100 AS FalsePercentage
, (COUNT(
) - (CAST(SUM(“TRUE Flag”) AS FLOAT))) / (CAST(SUM(“TRUE Flag”) AS FLOAT)) * 100 AS TruePercentage
FROM RiskAdjust_Temp.dbo.WHP_Mem_Lev_Conf1;

Any help would be most appreciated.

Thank you.

,(((CAST(SUM(“TRUE Flag”)) AS FLOAT) / COUNT(*)) * 100) AS TruePercentage

This needs another cast statement. Here it is corrected:

,cast (((CAST(SUM(“TRUE Flag”)) AS FLOAT) / COUNT(*)) * 100) AS TruePercentage

Terrific, thank you Jennifer.