# 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] rrWHERE [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_INSFFROM [dbo].[return_rows] rrWHERE [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.00r_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.00select  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_INSFFROM [dbo].[return_rows] rrWHERE [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] rrWHERE [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_INSFFROM [dbo].[return_rows] rrWHERE [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