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)

Group by Query

Hi,

Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.

Now I want get sum of both for a particular manager on a daily basis. Like

SaleDate sum(Directsales) sum(TeammemSales)

if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.

How to achieve this task in query?

Any help is appreciated.

Thanks

M.L.SrinvasNot sure if I'm understanding here...but I think you just need an IF statement at the beginning of the procedure to test if the id is empid or TeamLeaderID. You can then run the appropriate select based on which ID was passed in.

Group By clause killing performance

I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.

Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.

Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.

If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?

Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?

I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

It is a combination of few factors.(1) It is imptant to have proper indexes on columns being queried in WHERE and GROUP BY (2) Also see if you can move the GROUP BY to the reporting tool? (3) Is it the GROUP BY thats killing it or something else? Are you using any functions on columns in the WHERE? like some CONVERT(Datecolumn,100) >= '2007/01/01' etc?

|||

It is definetly the group by that is killing it. When the query was rewritten to to remove the group by the execution time dropped through the floor.

from 1 minute to less than 1 second.

Here is a sudo example of what I mean

old query first

SELECT
column1,
column2,
column3,
SUM(something)
FROM
table1
inner join table 2 on 1.columna = 2.columnb
GROUP BY
column1,
column2,
column3

new query

SELECT
column1,
column2,
column3,
(SELECT SUM(something) From sometable) AS 'blah'
FROM
table1
inner join table 2 on 1.columna = 2.columnb

I know that there huge gap between what is really going on and the code above but you get the main idea. moving the sum to a select so that the group by is no longer required. This and this allow drastically reduced the amount of time that it took to get the data. I knew that group by was expensive I just didn't realize how expensive it was.

|||

The group by clause is not in itself a bad performer. There is something else at work but without more detail, I can't tell you what.

The two queries you gave aren't the same thing. The second query doesn't do a sum based on the contents of the current row (No where clause relating the two). Which then of course it runs much faster, it's only executing the sum once, and using it on every row of the outer query.

|||

No mystery. "If I cannot do a reasonable query of a couple of million records..."

Sorting a couple of million records is, well, expensive! That's what a group by does, it sorts. And you don't even have a where clause to limit the answer set.

If you put a clustered index on column1, column2, column3 it will be able to avoid the sort, but you need to look at that carefully since it may have an impact on other queries (and you may already have a clustered index)

|||

True, the second query is unsorted (Not that common to request a set of data and not care about it's sort order), which will obviously be a completely different query plan. I would venture to guess that you don't have a good index on the table either that can/will help you.

Instead of putting a clustered index on the table, if you put an index on column1,column2,column3 and the field you are summing, your query time will drop significantly as well.

Faster yet, would be to use an indexed view.

|||

I am hearing basically what I thought I would hear. GROUP BY equals SORTING, a couple million records is a lot of data, no need to aviod GROUP BY like the plauge, check the indexes and statistics too.

Thanks. There is never a right or wrong answer to this kind of thing, it always depends on the shop and the database.