asked on

Adding and Dividing Problem

SELECT
100.0 * CAST(
SUM(CASE WHEN ap.completed = 'N' THEN 1.0 ELSE 0.0 END) AS float)
/

(SELECT count(*)
FROM xref_academy_profile_period
where academy_period_id = 24)

AS Incomplete

FROM Xref_academy_Period x
INNER JOIN Academy_Profiles AP ON ap.academy_id = x.academy_id
AND ap.academy_period_id = 24

I am trying to take the number(sum of) of records that have a value of "N" in this column and divide it by the total count of a query on another table (the xref_academy_profile_period table). I think and well, I know my syntax is wrong or I am at least not getting the expected result.

Any help appreciated

-ws

appari

did you run the query, seems ok to me.
are you getting errors when you execute or the results are not expected results?
can you post sample data and the result you are looking for and result returned by your query?

Westside2004

ASKER

Hi,

I do not get an error, I get unexpected results. I get a result of "100" I should get:

0.0175 or 1.75% essentially

/* should return a value of 1, because there is only 1 record in the table where sp.completed = 'N' */
SELECT
100.0 * CAST(
SUM(CASE WHEN sp.completed = 'N' THEN 1.0 ELSE 0.0 END) AS float)

\

There are 57 total records returned in this code (below):

(SELECT count(*)
FROM xref_school_profile_period x
where x.pp_id = 12)

It SHOULD be 1 / 57. but the problem is the first part (see below) t, is not returning "1" I don't think.

SELECT
100.0 * CAST(
SUM(CASE WHEN sp.completed = 'N' THEN 1.0 ELSE 0.0 END) AS float)

The academy_profiles table has 1 record in it with a academy_period_id of 24 and the value of the "completed" column in this table is set to 'N'.

Hope that makes sense.

-ws

ASKER CERTIFIED SOLUTION

itdrms

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial