March 12, 2015 at 12:57 pm
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?
March 12, 2015 at 1:10 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply