Count of distinct records in a group

you can do something like this:

count(distinct fieldname)

Yes, just replace the line

count(1) as CNT

with

count(distinct tblautosystemid) as CNT

...pretty much the exact thing I just posted reb....

reb73:  I was going to respond to this question about 45 minutes ago but decided against it since chapmandew had already posted it before I had opened it.  I forgot to close it and came across this tab so i decided to refresh it and see if it was closed or if they had any follow up questions.

Why am I not surprised to see that you, once again like you have done to me many times, have posted the same answer that another expert has already provided.

Apologies Tim, didn't mean to take credit for your post, just suggested where/what to change..

MarkKothe -> No points required for my post, chapmandew's post should be the one accepted..

Brandon, I've apologised to Tim already.

"Why am I not surprised to see that you, once again like you have done to me many times, have posted the same answer that another expert has already provided"

That's petty, care to substantiate this allegation?

>Brandon, I've apologised to Tim already.
As of when I posted that, nearly 25 minutes after you "mistakenly" made the post, you had not.

>That's petty, care to substantiate this allegation?
Don't need to.  Your posts speak for themselves.

"Don't need to.  Your posts speak for themselves."

If my posts speak for themselves, I can live with that.. But I can't understand why you feel it necessary to make allegations and hijack this thread..

reb: in an effort to not clutter ee, if you feel this needs discussed further contact me here.

http://sqlservernation.com/members/BrandonGalderisi/default.aspx

I tried count(tblAutoSystem.AutoSystemID) before I posted it and I get the same results of 3.
Mark

Try out this Slightly modified query:

SELECT AutoSystemID, AutoSystemDescription, count(*) cnt
from (
select tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription
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 ) temp
group by AutoSystemID, AutoSystemDescription

Things changed in your query:

1. Usage of Distinct, Group by and COUNT in a single level. Group by and count wont eliminate duplicate records and applying Distinct on the grouped records will not remove duplicates again.
2. Hence I have handled removing duplicates in the inner query and count in the outer query.

Hope this helps you out.

I got the error
An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
So I added one for now.

Then it did not like the table name temp, so I changed it.

Now I get

71      Automatic Trans, Coolers, Torque Converter      1  <-- OK
67      Brakes, Power Brakes, Traction Control      1  <--- OK
82      Chassis Electrical, Wiring, Fuses & Breakers, Wipers, Motors      1 <-- Should be 5

Here is the query as I modified it.  

SELECT AutoSystemID, AutoSystemDescription, count(*) cnt
from (
select top 5 tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription
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 ) temptest
group by AutoSystemID, AutoSystemDescription

So then I added another file to be able to change the grouping on the inner query. Now the query looks like :

SELECT AutoSystemID, AutoSystemDescription, count(*) cnt
from (
select tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription, FileNamePDF
from tblTSBToVehicleXref
join tblTSBToAutoSystemXref on tblTSBToVehicleXref.tsbID=tblTSBToAutoSystemXref.tsbID
join tblAutoSystem on tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID
join tblTSB on tblTSB.TSBID=tblTSBToVehicleXref.TSBID
where vehicleID in ( '1001114','1001125','1001136')
group by FileNamePDF, tblAutoSystem.AutoSystemID, tblAutoSystem.AutoSystemDescription
 ) temptest
group by AutoSystemID, AutoSystemDescription
order by AutoSystemDescription

The results are as follows:

71
Automatic Trans, Coolers, Torque Converter      1      67
Brakes, Power Brakes, Traction Control      1      82
Chassis Electrical, Wiring, Fuses & Breakers, Wipers, Motors      5      

These are all correct! Yea!

I learned from your changes, Raja. Thank you very much!

Mark

Yes, the only thing driving me crazy now is the results are coming back different between Query Analyzer and the adodb connection in the app. The QA results are correct, so must be something in the app.

I'll figure it out.

Thanks again.
Mark