Solved

Count (distinct ...) in SQL

Posted on 2004-08-12
8
659 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

740 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