SQL Error: Derived column which calculate percentage was returning 0 for every rows

Scenario

I had a valid sql query to calculate percentage from two columns which were holding integer value,

Actual.PossibleAttendance/ Possible.PossibleAttendance AS Percentage

But it has not worked.

Another scenario was, I wanted to get derived columns with 2 digit decimal points

Solution

I converted the data into decimal points and then it worked.

CONVERT(DECIMAL(5,2), CONVERT(DECIMAL, Actual.PossibleAttendance) / CONVERT(DECIMAL, Possible.PossibleAttendance))*100 AS Percentage

ROUND(CAST(Actual.PossibleAttendance AS FLOAT) / CAST(Possible.PossibleAttendance AS FLOAT),4)*100 AS Percentage

####################

  1. select cast(3 as float)/cast(2 as float);
  2. select convert(float, 3)/convert(float, 2);
  3. select convert(decimal(5, 2), convert(decimal, 3)/convert(decimal, 2))
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s