math issue

  • itmasterw 60042

    SSCrazy

    Points: 2792

    Hi,

    I am applying a formula which separately works but is not working when I put it together is one SQL Statement.  At the end of the code below, I am getting 1.002 and it should be 47.83

    Any ideas as to what I am doing worng??

    Thank you

       SELECT CAST((Count(rr.[company_name]) + 1)  AS decimal(10,2))
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    AND rr.[company_name] = 'Cash4WhateverTX'
    --Results = 4088.00

    SELECT NULLIF(CAST((Count(rr.[company_name]) + (Count(*) - ( Count(rr.[company_name])))) AS decimal(10 ,2)),0) AS Percent_INSF
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    --Results = 8546.00

    SELECT (CAST((4088) AS decimal(10 ,2))/CAST((8546) AS decimal(10 ,2))) *100
    --Results = 47.835%

    SELECT rr.[company_name],
    (CAST((Count(rr.[company_name]) + 1) AS decimal(10,2))/NULLIF(CAST((Count(rr.[company_name]) + (Count(*) - ( Count(rr.[company_name])))) AS decimal(10 ,2)),0)) AS Percent_INSF
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    AND rr.[company_name] = 'Cash4WhateverTX'
    GROUP BY rr.[company_name]
    --Results = 1.002
  • Steve Collins

    SSC Eights!

    Points: 889

    ;with
    c4w_cte(co_name_count) as (
    SELECT CAST((Count(rr.[company_name]) + 1) AS decimal(10,2))
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    AND rr.[company_name] = 'Cash4WhateverTX'),
    --Results = 4088.00
    r_cte(pct_insf) as (
    SELECT NULLIF(CAST((Count(rr.[company_name]) + (Count(*) - ( Count(rr.[company_name])))) AS decimal(10 ,2)),0) AS Percent_INSF
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01')
    --Results = 8546.00
    select
    cc.co_name_count/r.pct_insf*100
    --Results = 47.835%
    from
    c4w_cte cc
    cross join
    r_cte r;

    • This reply was modified 6 months ago by  Steve Collins. Reason: Mis-pasted
  • itmasterw 60042

    SSCrazy

    Points: 2792

    Hi

    First, thanks for the reply, I like the CTE here.

    Unfortunately, I need this to be in one statement like this:

    SELECT rr.[company_name],
    (CAST((Count(rr.[company_name]) + 1) AS decimal(10,2))/NULLIF(CAST((Count(rr.[company_name]) + (Count(*) - ( Count(rr.[company_name])))) AS decimal(10 ,2)),0)) AS Percent_INSF
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'

    I am sorry, I should have told you that I am trying to get this going for one company first. but I have about 15 companies that I will be doing this in a Case when (like Case When rr.[company_name] =  'Cash4WhateverTX') then do the line of code above.

    Unless there is a way to do it in the CTE?

    Thank you

  • Steve Collins

    SSC Eights!

    Points: 889

    Does this return the correct answer?

    declare
    @company_name varchar(36)='Cash4WhateverTX',
    @return_code varchar(5)='R01';

    SELECT
    cast((sum(case when rr.[company_name]=@company_name then 1 else 0 end) + 1.0)/count(*) as decimal(10, 2))
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = @return_code;
  • ScottPletcher

    SSC Guru

    Points: 98490

    See if this works for you:

    ;WITH cte_raw_counts AS (
    SELECT COUNT(*) AS total_company_count,
    SUM(CASE WHEN rr.[company_name] = 'Cash4WhateverTX' THEN 1 ELSE 0 END) AS [Cash4WhateverTX],
    SUM(CASE WHEN rr.[company_name] = 'Name2' THEN 1 ELSE 0 END) AS [Name2] /*, ...*/
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    )
    SELECT
    CAST(Cash4WhateverTX * 100.0 / total_company_count AS decimal(5, 2)) AS [Cash4WhateverTX%],
    CAST(Name2 * 100.0 / total_company_count AS decimal(5, 2)) AS [Name2%] /*, ...*/
    FROM cte_raw_counts

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • drew.allen

    SSC Guru

    Points: 76739

    The issue that you're running into is that the two separate queries have different criteria and you aren't taking that into account when you are combining them into one query.  (I'm also not sure why you are adding one to the count.)

    You're also trying to do this in a procedural manner rather than a set-based manner.  You're trying to do it separately for each company instead of processing all of the companies at the same time.

    I believe that the following will give you what you need.

    SELECT
    rr.[company_name],
    CAST(Count(rr.[company_name]) AS decimal(10,2))/NULLIF(SUM(CAST(Count(rr.[company_name]) AS decimal(10 ,2))) OVER(), 0) AS Percent_INSF
    FROM [dbo].[return_rows] rr
    WHERE [return_code] = 'R01'
    GROUP BY rr.[company_name]

    Note that the code uses a windowed function SUM() OVER() of an aggregate COUNT().

    Drew

    PS:  A CTE is PART OF the following statement, so using a CTE with a SELECT is using a single statement.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply