Solved

Count (distinct ...) in SQL

Posted on 2004-08-12
8
652 Views
Last Modified: 2010-05-18
I have a query that's supposedly counting distinct patients but when I count(distinct patientid) separate from the rest of the query it's obviously not working because the distinct patient count is lower.  Can someone tell me what I'm doing wrong?  

SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,
                      AccountTypeCd, Activity_Flag, count(distinct PatientId) as Patcount, Activity_Name,  Time_id, the_month, acuityflag
INTO dbo.FACT_ETMI_monthly_Datamart_PC
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1
GROUP BY PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,
AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
ORDER BY PayorName, time_id, LOBPAYOR, Program, Activity_Flag,AcuityFlag
0
Comment
Question by:artgal
8 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 11785483
One patient in multiple programs, timeIds,...
0
 
LVL 4

Expert Comment

by:jrandallsexton
ID: 11785520
You might could do something like:
SELECT PayorID, PayorName, ..... SELECT A.* FROM ((your other count query here)A), ...

Not sure if that will work or not.

0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11785635
Please describe for us what you want in the Patcount column. Assuming the overall SELECT-INTO query is going to insert multiple rows, do you want Patcount to have the same value in each row, that is, the total of distinct patientid's without any conditions?

If so, try this:
SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag,
 (SELECT count(distinct PatientId) FROM dbo.FACT_ETMI_monthly_Datamart_PC1) as Patcount,
 Activity_Name,  Time_id, the_month, acuityflag
INTO dbo.FACT_ETMI_monthly_Datamart_PC
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1
ORDER BY PayorName, time_id, LOBPAYOR, Program, Activity_Flag,AcuityFlag

If not, we need more explanation as to your goal.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 11785648
I am not sure you want count halves of patients, but maybe.

if

PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
columns are not null

then (else you must set ANSI_NULLS off or rewrite ON clause)
-------------------------------------------------------------------------------------------------------------------------------------------

SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, count(distinct A.PatientId)/Cnt as Patcount
, Activity_Name,  Time_id, the_month, acuityflag
INTO dbo.FACT_ETMI_monthly_Datamart_PC
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1 A
JOIN
(
select PatientId,cast(count(*) as float) Cnt from (
SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
, PatientId
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1
GROUP BY PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
) X
) B
ON
    A.PayorID=B.PayorID
AND A.PayorName=B.PayorName
AND A.LOBPAYOR=B.LOBPAYOR
AND A.Program=B.Program
AND A.PayorTypeCd=B.PayorTypeCd
AND A.AccountTypeCd=B.AccountTypeCd
AND A.Activity_Flag=B.Activity_Flag
AND A.Activity_Name=B.Activity_Name
AND A.Time_id=B.Time_id
AND A.the_month=B.the_month
AND A.acuityflag=B.acuityflag
GROUP BY PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
ORDER BY PayorName, time_id, LOBPAYOR, Program, Activity_Flag,AcuityFlag
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 13

Accepted Solution

by:
ispaleny earned 250 total points
ID: 11785723
Sorry, I posted some older select. This is the right one.

SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, sum(1/Cnt) as Patcount
, Activity_Name,  Time_id, the_month, acuityflag
INTO dbo.FACT_ETMI_monthly_Datamart_PC
FROM  (
 SELECT  distinct PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
 , PatientId
 FROM   dbo.FACT_ETMI_monthly_Datamart_PC1
) A
JOIN
(
select PatientId,cast(count(*) as float) Cnt from (
SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
, PatientId
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1
GROUP BY PatientId
) X
) B
ON A.PatientId=B.PatientId
GROUP BY PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd, AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
ORDER BY PayorName, time_id, LOBPAYOR, Program, Activity_Flag,AcuityFlag
0
 

Author Comment

by:artgal
ID: 11788794
What I realized is that ispaleny was correct, there were patients in more than one program ... which is impossible.  The person who wrote the original query is helping to eliminate those duplicates.  I'm going to leave it open for another day to make sure that I don't need to eliminate the dupes myself (in which case I'll figure out what I need and come back here) but otherwise I'll give the points to ispaleny.  Thanks so much.  :)
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11791506
Is your column called distinct PatientId? if no then you do it as below,
SELECT  PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,
                      AccountTypeCd, Activity_Flag, count(distinct (PatientId)) as Patcount......
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11791541
Sorry.........I stupidly thought..................ok
see this

select count (PatientId) as Patcount , PayorID, PayorName, LOBPAYOR, Program, PayorType, AccountTypeCd, Activity_Flag, PatientId , Activity_Name,  Time_id, the_month, acuityflag
INTO dbo.FACT_ETMI_monthly_Datamart_PC
FROM   dbo.FACT_ETMI_monthly_Datamart_PC1 where PatientId in (SELECT distinct PatientId FROM   dbo.FACT_ETMI_monthly_Datamart_PC1)
GROUP BY PayorID, PayorName, LOBPAYOR, Program, PayorTypeCd,
AccountTypeCd, Activity_Flag, Activity_Name,  Time_id, the_month, acuityflag
ORDER BY PayorName, time_id, LOBPAYOR, Program, Activity_Flag,AcuityFlag
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now