Group By Question

I am writing a very simple polling application and I am trying to return the
results in which it will count the number of responses for a given survey.
What I wrote was:
SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
GROUP BY SurveyChoice.Choice;
This is good because it returns Choices even if they were not used (i.e.,
nobody voted for a particular option and the Quantity is returned as 0).
But if I add the following, I lose all of the Quantity=0 records.
HAVING SurveyVote.FK_SurveyID=1
Is there a way to have both?
DaveMaybe
HAVING (SurveyVote.FK_SurveyID=1 OR COUNT(SurveyVote.FK_SurveyChoiceID)=0)
http://www.aspfaq.com/
(Reverse address to reply.)
"David Mroz" <dave@.glimmernet.com> wrote in message
news:OWUlBefGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> I am writing a very simple polling application and I am trying to return
the
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS
Quantity
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>|||David,
I am a little bit . The column FK_SurveyID is not in the select
list, neither in the group by clause, I wonder why sql server is not giving
an error?
If you want to filter the rows using WHERE clause, then you can use "GROUP
BY ALL SurveyChoice.Choice" to see all groups even the ones with no row that
meet the filter.
SELECT
SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM
SurveyVote
RIGHT JOIN
SurveyChoice
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
where
SurveyVote.FK_SurveyID = 1
GROUP BY
SurveyChoice.Choice;
AMB
"David Mroz" wrote:

> I am writing a very simple polling application and I am trying to return t
he
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantit
y
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>
>|||On Wed, 23 Feb 2005 17:31:57 -0500, David Mroz wrote:

>I am writing a very simple polling application and I am trying to return th
e
>results in which it will count the number of responses for a given survey.
>What I wrote was:
>SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
>FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
>SurveyChoice.PK_SurveyChoiceID
>GROUP BY SurveyChoice.Choice;
>This is good because it returns Choices even if they were not used (i.e.,
>nobody voted for a particular option and the Quantity is returned as 0).
>But if I add the following, I lose all of the Quantity=0 records.
>HAVING SurveyVote.FK_SurveyID=1
>Is there a way to have both?
Hi Dave,
In addition to the answers by Aaron and Alejandro, I *think* the
following will work as well. They are untested, though, since you didn't
provide CREATE TABLE and INSERT statements to create a test set.
1. Using LEFT JOIN instead of RIGHT JOIN
SELECT SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyChoice
LEFT JOIN SurveyVote
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1
GROUP BY SurveyChoice.Choice
2. Using subselect instead of join
SELECT SurveyChoice.Choice,
(SELECT Count(SurveyVote.FK_SurveyChoiceID)
FROM SurveyVote
WHERE SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1) AS Quantity
FROM SurveyChoice
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)