Need to create a new column by dividing 2 columns in the query

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 12, 2015 at 12:57 pm

  #316224

  I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]

  /ProviderTotal) * 100

  SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]

  FROM HS140_Rpt_Tmp_ForSummary s

  WHERE s.[Security] = 'Yes'

  GROUP BY Market

  How can I do this?

• LoudClear

  SSC Eights!

  Points: 999

  More actions

  March 12, 2015 at 1:10 pm

  #1783706

  Here's one solution. Excuse my simplistic sample data. I wasn't sure by what conditions an NPI might not be distinct, so I included some duplicates to demonstrate that they would only be counted once, since you did include the distinct keyword in your query.

  - Adam

  with HS140_Rpt_Tmp_ForSummary as

  (

  SELECT'Mkt1' as Market, 'ASDF'as NPI, 'No' as [Security]

  unionSELECT'Mkt1' as Market, 'qwerty'as NPI, 'Yes' as [Security]

  unionSELECT'Mkt1' as Market, 'qwerty'as NPI, 'Yes' as [Security]

  unionSELECT'Mkt1' as Market, 'qwerty'as NPI, 'Yes' as [Security]

  unionSELECT'Mkt2' as Market, 'zxcv'as NPI, 'Yes' as [Security]

  unionSELECT'Mkt2' as Market, 'A1B2C3'as NPI, 'No' as [Security]

  unionSELECT'Mkt2' as Market, 'FooBar'as NPI, 'No' as [Security]

  )

  SELECT

  COUNT(DISTINCT NPI) AS ProviderTotal

  ,COUNT(DISTINCT case when s.[Security] = 'Yes' then NPI else null end) AS [Providers With Security]

  ,COUNT(DISTINCT case when s.[Security] = 'Yes' then NPI else null end) * 1.0 / COUNT(DISTINCT NPI)

  FROM HS140_Rpt_Tmp_ForSummary s

  GROUP BY Market