messaging an existing sql query to include a count column

I have a large SQL query that returns a classic asp ADO.Recordset. I want to add a column to the query that simply contains a count of all the distinct PublicationID's along with all the other data, how to? Below is just a sample as I don't want to paste the query due to it's size etc. I kno开发者_如何学Gow I want to use COUNT(DISTINCT na.publicationid) but I am not sure how to shoehorn it into an existing query, return the existing data but with an additional column.

SELECT na.publicationid, na.nameabbrev
FROM NewspaperAd as na
WHERE na.publicationid = '12345'

I want to add something like:

SELECT na.publicationid, na.nameabbrev, COUNT(DISTINCT na.publicationid) as distnct_p_id
FROM NewspaperAd as na
WHERE na.publicationid = '12345'

As it stands, your "count" query makes no sense. You want to see "12345", plus the "nameabbrev" column for NewspaperAd 12345, and you want to see the number of publications? over all rows? That's two separate queries.

You need to Seperate the Query:

SELECT na.publicationid, na.nameabbrev
,(Select COUNT(publicationid) 
FROM NewspaperAd 
WHERE publicationid = na.publicationid) as distnct_p_id
FROM NewspaperAd as na
WHERE na.publicationid = '12345'
group by na.publicationid, na.nameabbrev