Query data not tying out

Question

Post reply

Query data not tying out

wheddingsjr

Old Hand

Points: 355
More actions
October 29, 2021 at 12:24 am

#3945767
Hello all
I have written to short and basic queries that should tie, but for some reason they do not for some reason. Here is the first simple query. With this query I get a count of 58,654
```
SELECT COUNT (DISTINCT [UniqueClaimID]) AS UniqueClaimID
  FROM [phm_db].[dbo].[PCP2Final]
```
The second query is:
```
SELECT COUNT (DISTINCT [UniqueClaimID]) AS UniqueClaimID, ProvOrgNm
FROM [phm_db].[dbo].[PCP2Final]
GROUP BY ProvOrgNm
```
When I run this query, the total count when I add the UniqueClaimID's for each of the ProOrgNm, I end up with 211,501. How is that possible? Any and all assistance would be greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

GROUP BY/COUNT DISTINCT ignores NULL in a column, so you probably have NULLs in your UniqueClaimID column.

Try this

SELECT COUNT (DISTINCT ISNULL([UniqueClaimID], '0')) AS UniqueClaimID, ProvOrgNm AS ProvOrgNm
FROM [phm_db].[dbo].[PCP2Final]
GROUP BY ProvOrgNm

I'm on LinkedIn

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

-- try
SELECT [UniqueClaimID], count(distinct ProvOrgNm) nProvOrgNm, count(*) nRows
FROM [phm_db].[dbo].[PCP2Final]
group by [UniqueClaimID]
having count(*) > 1
order by nRows desc
;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

wheddingsjr Old Hand Points: 355 More actions · Answer 3

Thanks PB

That gives me the exact count I am currently getting with my code.

wheddingsjr Old Hand Points: 355 More actions · Answer 4

Thanks Johan

Unfortunately that did not work. My results should look like this:

Your codes results look like this:

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

What I wanted to point to is that a given UniqueClaimID e.g. 10929_6155... is being counted 5 times as there are 5 ProvOrgNm using it, on a total of 134865 rows for that given UniqueClaimID .

So counting the individual ProvOrgNm for distinct UniqueClaimID may indeed produce a different total if a given ProvOrgNm can reference more than on UniqueClaimID in the total set.