Solved

Count (distinct ...) in SQL

Posted on 2004-08-12
8
654 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

825 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