Avatar of MarkKothe
MarkKothe

asked on 

Count of distinct records in a group

The query:

select distinct
tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription ,
count(1) as CNT
from tblTSBToVehicleXref
join tblTSBToAutoSystemXref on
tblTSBToVehicleXref.tsbID=tblTSBToAutoSystemXref.tsbID
join tblAutoSystem on
tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID
where vehicleID in ( '1001114','1001125','1001136')
group by tblAutoSystem.AutoSystemDescription,tblAutoSystem.AutoSystemID
order by tblAutoSystem.AutoSystemDescription

Returns:
71 Automatic Trans, Coolers, Torque Converter    3

The number on the end of the data is the count of the records in each group

The actual DISTINCT count of the 2 fields is 1.

The following query shows the actual data.

select VehicleID, tblTSBToVehicleXref.TSBID,
tblTSBToAutoSystemXref.AutoSystemID, AutoSystemDescription
 from tblTSBToVehicleXref
 join tblTSBToAutoSystemXref on
tblTSBToVehicleXref.TSBID=tblTSBToAutoSystemXref.TSBID
 join tblAutoSystem on
tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID
where vehicleID in('1001114','1001125','1001136') and
tblAutoSystem.autosystemid='71'

1001114 7404    71    Automatic Trans, Coolers, Torque Converter

1001125 7404    71    Automatic Trans, Coolers, Torque Converter

1001136 7404    71    Automatic Trans, Coolers, Torque Converter

What am I doing wrong?
Do I need to a a subquery in place of the count() to get the correct number?
DatabasesMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
MarkKothe

8/22/2022 - Mon