[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1931
  • Last Modified:

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?
0
funaroma
Asked:
funaroma
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need something like this... not sure if you need/want the where condition also there...

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
0
 
funaromaAuthor Commented:
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.questionSequence, PlenarySurvey.choiceSequence

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
0
 
funaromaAuthor Commented:
and further, would it be even more efficient to include the COUNT (DISTINCT) clause in the VIEW, rather than essentially querying the view twice?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT     ps.*
, ( select COUNT(DISTINCT i.questionPK)
    from PlenarySurvey i
   WHERE     i.surveyPK = ps.surveyPK
      AND  i.pageNumber = ps.pageNumber
 ) AS questionTotal
FROM         PlenarySurvey ps
WHERE     ps.surveyPK = 1
AND ps.pageNumber = 2
ORDER BY ps.questionSequence, ps.choiceSequence
0
 
funaromaAuthor Commented:
Fast and concise... thank you!!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now