Solved

Count (distinct ...) in SQL

Posted on 2004-08-12
8
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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!

 
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

Independent Software Vendors: 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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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