Group by clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group by clause

Author

Topic

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-22 : 11:26:09

I need some help on my SQL query:

Here is my query:
SELECT que.que, que.QueID, ans.AnsID, ans.AnsID AS Expr1, SUM(OrgAnswers.totalHits) AS Total
FROM OrgAnswers INNER JOIN
ans ON OrgAnswers.AnsID = ans.AnsID INNER JOIN
que ON ans.QueID = que.QueID
WHERE (ans.QueID = 76) OR
(ans.QueID = 77)

After I executed, this error showed up:

************************************
Colume Que.que is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
************************************

First of all, I would like to understand what the error means

Second of all, can someone please point me to a good website for "GROUP BY"?

Thank you.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-22 : 11:32:50

Your solution should look like this:
SELECT que.que, que.QueID, ans.AnsID, ans.AnsID AS Expr1, SUM(OrgAnswers.totalHits) AS Total
FROM OrgAnswers INNER JOIN
ans ON OrgAnswers.AnsID = ans.AnsID INNER JOIN
que ON ans.QueID = que.QueID
WHERE (ans.QueID = 76) OR
(ans.QueID = 77)
GROUP BY que.que, que.QueID, ans.AnsID, ans.AnsID

1.) Any form of aggregation (Sum, Count etc) must happen at a given level.
In this example you will get the sum of totalhits for each distinct value of the combination of
que.que, que.QueID, ans.AnsID, ans.AnsID.

2.) Books On Line is a good place to look. This site is fantastic too.
google puts the SQL world at your fingertips.

Duane.

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-22 : 11:41:50

Thank you very much for your help :)

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-22 : 13:39:42

One more (?) please :)

Here is my sql:

SELECT AnsGroup.ansgroupdesc, AnsGroup.ansgroupID, SUM(OrgAnswers.totalHits) AS Total

FROM AnsGroup INNER JOIN
ans ON AnsGroup.ansgroupID = ans.ansGroupID INNER JOIN
OrgAnswers ON ans.AnsID = OrgAnswers.AnsID

WHERE (ans.QueID = 76) AND (ans.ansGroupID = 1 OR
ans.ansGroupID = 2 OR ans.ansGroupID = 3)

GROUP BY AnsGroup.ansgroupdesc, AnsGroup.ansgroupID
ORDER BY AnsGroup.ansgroupID

This SQL works just fine but I am not happy with the WHERE clause. Why? Well, I have a table like this:
ansGroupsID ansGroupDesc
1 Good
2 Bad
3 Worse
4 N/A

And I want to display Good, bad and worse only. How can I write my WHERE clause to achieve this???? My WHERE clause does this but I wonder if there is a different way to get the same result.

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 13:45:56

WHERE ans.ansGroupID <> 4

OR

WHERE ans.ansGroupDesc <> 'N/A'

?

Do you really need an identity column for your ans table?

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-22 : 13:50:04

Oh, yeah. That's exactly what I am looking for, the <> sign.
Tara, you asked me this: "Do you really need an identity column for your ans table?"

My answer is: I think I do. I think each table should have an identity column. Am I wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 13:53:17

Yes you are wrong. There are times when you need them and times when you do not. You need them when there is no other candidate for the natural key. Your candidate is the ansGroupDesc. That should be your primary key. I would further argue that you don't even need an ans table though. Why not just store the actual value of ansGroupDesc in your ansGroup table?

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-22 : 14:53:56

Tara, this is what you wrote: "Your candidate is the ansGroupDesc. That should be your primary key. I would further argue that you don't even need an ans table though. Why not just store the actual value of ansGroupDesc in your ansGroup table?"

Before I answer your question, I would like to show you my design and then you can tell me whether or not I need an 'ans' table :)

Here is what my 'ans' table look like:

ansID (PK)
queID (FK to 'que' table)
CategoryID (FK to 'Category' table)
ansdesc (description of the answer)
ansGroupID (FK to 'ansgroup' table)

Here is what my 'ansgroup' table look like:

ansgroupID (PK)
ansgroupdesc (descrtiption of the answer)

"Why not just store the actual value of ansGroupDesc in your ansGroup table?"

I did store the actual value of ansGroupDesc in my 'ansGroup' table. In my 'ansgroup' table, I have 2 fieldnames: 1) ansgroupID 2) ansgroupdesc.

I think I can group these 2 tables into 1 big table and the new table would look something like this:

ansID (PK)
queID (FK to 'que' table)
CategoryID (FK to 'Category' table)
ansdesc (description of the answer)
ansGroupID (FK to 'ansgroup' table)
ansgroupdesc

I think that should work as well but the reason that I want to separate them into 2 tables is that if the users want to modify the text for ansgroupdesc, all I have to do is to go to 'ansgroup' table and change 3 rows only. If I have only 1 table and store everything, then I have to go to each row and modify the changes.

Fx: here is the actual data for the 'ans' table:
ansID queID CategoryID ansdesc ansGroupID
1 1 1 Very great extent 1
2 1 1 Great extent 1
3 1 1 Moderate extent 2
4 1 1 Slight extent 2
5 1 1 None at all 3
6 1 1 Do not know 4
7 1 1 N/A 4

Fx: here is the actual data for the 'ansGroup' table:
ansgroupID ansgroupdesc
1 Good
2 Moderate
3 None
4 Noscore

Ok, if I combine 2 tables together, then I get this:
Fx: here is the actual data for the 'ans' table:

ansID queID CategoryID ansdesc ansGroupdesc
1 1 1 Very great extent Good
2 1 1 Great extent Good
3 1 1 Moderate extent Moderate
4 1 1 Slight extent None
5 1 1 Do not know NoScore
6 1 1 N/A NoScore

I don't think combining 2 tables into 1 is a very good idea. Fx in this case, what if the users change the ansgroupdesc for each answer, then it would be a nightmare to go back and make the change to every single row.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 15:56:33

I'm not saying that you shouldn't use identity columns as your primary key. I'm just saying that not every table needs one. For instance, say you have a state table. You want to store the state abbreviation and state name. It just doesn't make sense to have an identity column for this type of table as there is an obvious candidate key. You might not have one or it might take quite a few columns to get to one. We've got a table in one of our applications that takes about 10 columns for the primary key. Due to this table being involved in joins a lot, it made more sense for performance to use an identity column here.

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-23 : 12:42:12

Hummm.....I have executed this query in SQL server and I got the results I want. But when I implemented this on the web using ASP then I get a different result:

Here is what I see in SQL server when I run:

Great 198
Moderate 349
None 49

Here is what I see on the web when I run:

Great 147
Great 51
Moderate 236
Moderate 113
None 49

I want to combine 147 and 51 for 'Great'
and 236 and 113 for 'Moderate'

Does anyone know why I was able to combine this on the back end using SQL server and not on the front end????

Thank you in advance, Lisa

Here is my SQL queries and some asp codes :

********************************************************

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.CommandTimeout = 0
conn.open strConnect

' Retrieve questions, answers and totalhits
SQL="SELECT que.que, ans.ansID, AnsGroup.ansgroupdesc, AnsGroup.ansgroupID, SUM(OrgAnswers.totalHits) " & _
" FROM que INNER JOIN ans ON que.queID = ans.queID INNER JOIN " & _
" ansGroup ON ans.ansGroupID=ansgroup.ansgroupID INNER JOIN " & _
" OrgAnswers ON ans.AnsID = OrgAnswers.AnsID " & _
" WHERE (ans.QueID = 76) AND (ans.ansGroupID <> 4) " & _
" GROUP BY ans.ansID, que.que, AnsGroup.ansgroupdesc, AnsGroup.ansgroupID " & _
" ORDER BY AnsGroup.ansgroupID"

Set RS=Conn.execute(SQL)
' Write out the question
Response.Write RS("que")

Response.Write "<p>"

' Write a Do....While....Not loop to loop all the answers
Do While Not RS.EOF

ansTotalHit=RS("ansID")
SQL1="SELECT SUM(OrgAnswers.totalHits) " & _
" FROM que INNER JOIN ans ON que.queID = ans.queID INNER JOIN " & _
" ansGroup ON ans.ansGroupID=ansgroup.ansgroupID INNER JOIN " & _
" OrgAnswers ON ans.AnsID = OrgAnswers.AnsID " & _
" WHERE (ans.QueID = 76) AND (ans.ansGroupID <> 4) AND ans.ansID= " & ansTotalHit & _
" GROUP BY AnsGroup.ansgroupdesc, AnsGroup.ansgroupID " & _
" ORDER BY AnsGroup.ansgroupID"

' Execute SQL1
Set RS1=Conn.execute(SQL1)

Sum=RS1(0).value

' Write out the ansgroupdesc and sum
Response.Write RS("ansgroupdesc") & " " & Sum & "<br>"

' Move to the next answer
RS.MoveNext

' End the loop
Loop

' Close the RS
RS.close
Set RS=Nothing

' Close the connection
conn.Close
Set conn=Nothing

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-23 : 13:37:25

Please ignore my last post. I got it. Thx.

scotchbroom
Starting Member

17 Posts

Posted - 2004-04-23 : 16:04:21

Besides just using the keys for joining, I thought they were used because they take up less resources. A related table would only have to store an int instead of the long text descriptions. Is this a misconception?

Example:
1 Software Engineering
2 Human Resources
3 Sales
4 Finance
5 Project Operations

It's more conducive to use the int (key) field than to have the text repeated in a related field, is it not?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:17:33

It's been debated here a bunch:

http://www.sqlteam.com/item.asp?ItemID=2599
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136

Do a search on natural key in the forums and you'll see even more threads.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources