math issue

Question

Post reply

math issue

itmasterw 60042

SSCrazy

Points: 2858

February 12, 2020 at 6:52 pm

#3725205

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

February 15, 2020 at 3:04 am

This was removed by the editor as SPAM

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

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

Steve Collins SSCrazy Points: 2075 More actions · Answer 1

;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 4 years, 2 months ago by Steve Collins. Reason: Mis-pasted

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

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 SSCrazy Points: 2075 More actions · Answer 3

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;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

drew.allen SSC Guru Points: 76948 More actions · Answer 5

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