troubleshooting Question

Count of distinct records in a group

Avatar of MarkKothe
MarkKothe asked on
DatabasesMicrosoft SQL Server 2005SQL
15 Comments1 Solution345 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros