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
LVL 1
Westside2004Asked:
Who is Participating?
 
itdrmsCommented:
Totally positive

select count(*) from Academy_Profiles where completed = 'N' and academy_period_id = 24

returns 1?

Your 2nd copied version doesn't match your initial -- the one with a count of 57.  Your sure the actual version you are running with doesn't return 1?

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

FROM  Academy_Profiles AP
WHERE ap.academy_period_id = 24
returns 100 -- are you sure the divisor is not commented out?
0
 
appariCommented:
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?
0
 
Westside2004Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.