funaroma
asked on
Counting distinct values in one column of a view
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
FROM PlenarySurvey
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?
SELECT *, COUNT(DISTINCT questionPK) AS questionTotal
FROM PlenarySurvey
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?
ASKER
I stumbled upon a different method that gives me the same result:
SELECT PlenarySurvey.*, qc.questionTotal
FROM PlenarySurvey INNER JOIN
(SELECT pagePK, COUNT(DISTINCT questionPK) AS questionTotal
FROM PlenarySurvey AS PlenarySurvey_1
GROUP BY pagePK) AS qc ON PlenarySurvey.pagePK = qc.pagePK
WHERE (PlenarySurvey.surveyPK = 1) AND (PlenarySurvey.pageNumber = 2)
ORDER BY PlenarySurvey.questionSequ
So now the nature of my question changes a bit -- WHICH of these two choices is more efficient for the SQL Server to process? My solution above joins two tables but only requires passing in the surveyPk and pageNumber in one place
SELECT PlenarySurvey.*, qc.questionTotal
FROM PlenarySurvey INNER JOIN
(SELECT pagePK, COUNT(DISTINCT questionPK) AS questionTotal
FROM PlenarySurvey AS PlenarySurvey_1
GROUP BY pagePK) AS qc ON PlenarySurvey.pagePK = qc.pagePK
WHERE (PlenarySurvey.surveyPK = 1) AND (PlenarySurvey.pageNumber = 2)
ORDER BY PlenarySurvey.questionSequ
So now the nature of my question changes a bit -- WHICH of these two choices is more efficient for the SQL Server to process? My solution above joins two tables but only requires passing in the surveyPk and pageNumber in one place
ASKER
and further, would it be even more efficient to include the COUNT (DISTINCT) clause in the VIEW, rather than essentially querying the view twice?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fast and concise... thank you!!
SELECT *, ( select COUNT(DISTINCT questionPK) from PlenarySurvey WHERE (surveyPK = 1) AND (pageNumber = 2) ) AS questionTotal
FROM PlenarySurvey
WHERE (surveyPK = 1) AND (pageNumber = 2)
ORDER BY questionSequence, choiceSequence