Counting distinct values in one column of a view
Posted on 2007-11-30
I have a database view that joins several tables together. I am now selecting a recordset from this view, and I want to count the distinct number of one of the values in the result set generated by this view. For this question I suppose the view can simply be treated as a single table -- the fact that it's a view is really inconsequential. The query as I've attempted it is:
SELECT *, COUNT(DISTINCT questionPK) AS questionTotal
WHERE (surveyPK = 1) AND (pageNumber = 2)
ORDER BY questionSequence, choiceSequence
But this generates the dreaded
"Column 'abc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
I then tried adding all of the columns in the view to the GROUP BY clause, but then I got a different error, about text, ntext etc. not being valid in the group by clause (plus, that just doesn't "feel" like it would be the right answer anyway...)
How can I select all columns of the view (table), but also count distinct values of one of the view's (table's) columns, without screwing around with GROUP BY?