Adding and Dividing Problem

Posted on 2007-10-03
Last Modified: 2010-07-27
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

Question by:Westside2004
    LVL 39

    Expert Comment

    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?
    LVL 1

    Author Comment


    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' */
    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.

    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.

    LVL 6

    Accepted Solution

    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?

    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?

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now