Count() not working as expected in SQL

Question

A coworker put together a table for me that I need for mapping data, and table consists of two fields, OP_ID and BillType. In theory, there should be a one-to-one correspondence between the two: every Op_ID should have one and only one bill type.

When I started using it, however, I noticed that there were duplicate OP_IDs with different BillTypes. E.g:

OP_Id               BillType 
007a000v9GWkAAM BillReady 
007a000v9GWkAAM RateReady

So first I built a query to group the data into unique combinations of IDs and BillTypes:

SELECT OP_ID, BillType
FROM MappingTable
GROUP BY OP_ID, BillType

Simple enough. This set should and does include the above two records. Then, I wrapped another query around this to count OP_Ids within the aggregated dataset. Theoretically, any OP_ID with more than one BillType should occur twice, and so it should return a count > 1. Right?

SELECT OP_ID, BillType, Count(OP_ID)
FROM 
    (
    SELECT OP_ID, BillType
    FROM MappingTable
    GROUP BY OP_ID, BillType
    ) Base
GROUP BY  OP_ID, BillType
HAVING Count(OP_ID) > 1

But this query returns nothing whatsoever. Here's what even more baffling: when I remove the HAVING clause and limit the query to pull only the above OP_ID, since I already know it's a dupe, where's what I get:

OP_ID          BillType  CountOfOP_IDs
007a000v9GWkAAM BillReady   1
007a000v9GWkAAM RateReady   1

So there are clearly two records for OP_ID 007a000v9GWkAAM, but SQL is counting only one!

This seems so simple, and I'm sure I'm just missing something basic about how COUNT() works. For reference, I'm working on SQL Server 2014 and both columns are nvarchar. I've also confirmed that SQL evaluates the OP_IDs in both records as identical. Anyone know why this is happening?

Answer 1

Count counts the number of rows that have been grouped into one. Just remove bill type from the outer group by.

Also look at the count distinct option. That might be easier.