<div>
you can do something like this: 
 
count(distinct fieldname)
</div>

<div>
Yes, just replace the line 
 
count(1) as CNT 
 
with 
 
count(distinct tblautosystemid) as CNT 

</div>

<div>
...pretty much the exact thing I just posted reb....
</div>

<div>
reb73: &nbsp;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. &nbsp;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.
</div>

<div>
Apologies Tim, didn't mean to take credit for your post, just suggested where/what to change.. 
 
MarkKothe -&gt; No points required for my post, chapmandew's post should be the one accepted..
</div>

<div>
Brandon, I've apologised to Tim already. 
 
&quot;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&quot; 
 
That's petty, care to substantiate this allegation? 

</div>

<div>
&gt;Brandon, I've apologised to Tim already. 
As of when I posted that, nearly 25 minutes after you &quot;mistakenly&quot; made the post, you had not. 
 
&gt;That's petty, care to substantiate this allegation? 
Don't need to. &nbsp;Your posts speak for themselves.
</div>

<div>
&quot;Don't need to. &nbsp;Your posts speak for themselves.&quot; 
 
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.. 
</div>

<div>
reb: in an effort to not clutter ee, if you feel this needs discussed further contact me here. 
 
<a href="http://sqlservernation.com/members/BrandonGalderisi/default.aspx" rel="ugc">http://sqlservernation.com/members/BrandonGalderisi/default.aspx</a>
</div>

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

<div>
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
</div>

<div>
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. 
 

</div>

<div>
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 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Automatic Trans, Coolers, Torque Converter &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&lt;-- OK 
67 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Brakes, Power Brakes, Traction Control &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&lt;--- OK 
82 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chassis Electrical, Wiring, Fuses &amp;&nbsp;Breakers, Wipers, Motors &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &lt;-- Should be 5 
 
Here is the query as I modified it. &nbsp; 
 
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 
&nbsp;) temptest 
group by AutoSystemID, AutoSystemDescription 
order by AutoSystemDescription 
 
The results are as follows: 
 
71 
Automatic Trans, Coolers, Torque Converter &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;67 
Brakes, Power Brakes, Traction Control &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;82 
Chassis Electrical, Wiring, Fuses &amp;&nbsp;Breakers, Wipers, Motors &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
These are all correct! Yea! 
 
I learned from your changes, Raja. Thank you very much! 
 
Mark 

</div>

<div>
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
</div>

<div>
<div class="content wysiwyg-content">
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 &nbsp; &nbsp;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 
&nbsp;from tblTSBToVehicleXref 
&nbsp;join tblTSBToAutoSystemXref on 
tblTSBToVehicleXref.TSBID=tblTSBToAutoSystemXref.TSBID 
&nbsp;join tblAutoSystem on 
tblAutoSystem.AutoSystemID=tblTSBToAutoSystemXref.AutoSystemID 
where vehicleID in('1001114','1001125','1001136') and 
tblAutoSystem.autosystemid='71' 
 
1001114 7404 &nbsp; &nbsp;71 &nbsp; &nbsp;Automatic Trans, Coolers, Torque Converter 
 
1001125 7404 &nbsp; &nbsp;71 &nbsp; &nbsp;Automatic Trans, Coolers, Torque Converter 
 
1001136 7404 &nbsp; &nbsp;71 &nbsp; &nbsp;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? 

</div>
</div>

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?


Count of distinct records in a group

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.