Help with GROUP BY, please!!!

sledgeweb

I have the following SQL statement:

SELECT table1.id, table1.points, table1.section, table2.Name, table2.team
FROM table1
INNER JOIN table2 ON table2.ID = table1.id
WHERE table1.year = '2003'
GROUP BY table1.id
ORDER BY table1.points DESC, table2.Name ASC

Which doesn't work. If I take the GROUP BY out, it works. The problem is, I need the results to be grouped by ID. The reason is, the ID isn't unique in table 1, but it is in table 2. Table 1 contains points for ID's by section number. Table 2 is used to get the individuals name and team.

So, ID# 1234 could have 6 points in section 1, and 4 points in section 2. When I display the results, I want the points grouped together: ID # 1234 = 10 points. Table 2 is joined in to get the name of the person, so it would display:
John, Red Team has 10 points total. (another page would show which sections those points were from) If I take out the group by clase, I get two entries:
John, Red Team has 6 points total.
Jogn, Red Team has 4 points total.

If I use the group by, though, I get this message:
Column table1.points is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I get this error message for every item in the select list. BUT, I only want to group by the ID #!!! Argh... if I add the other items to the group by clause, it eliminates the error message, but doesn't display how I need it.

SO... how can I combine the results of my query so that all entries with the same ID are grouped into one result and still inner join the information from table 2?

Please help, this is driving me crazy!!

Thanks,
Sledgeweb

bad76

Hi,
with group statement every field must be a function like sum, avg, count or other.

For example in this case it's usefull SUM(table1.points).

BeastRider

I think that the problem is that you are trying to GROUP BY a variable that is not in the ORDERBY clause. Consider: If I have two fields in a table, state and city, and I say ORDER BY city, what happens? I get a report in city order... Akron, Colubus, Houston, etc. But, if I now add a GROUP BY state, what happens? Who knows? I can't group by state since the sort order is already city.

So I think you're query might work if it looks like this:

GROUP BY table1.id 
ORDER BY table1.id table1.points DESC, table2.Name ASC 
// note the addition of the id in the ORDER BY stmt

Let me know if this works.

sledgeweb

Thanks, but no go. It definitely has to do with the select list and group by clause. I tried your idea just to see what happened, but it didn't change the outcome.

Sledgeweb

ultraslacker

Bad76 is right - it is a rule in relational databases to maintain data integrity. If you use GROUP BY, how do you know which row is to be retrieved given multiple rows?

sledgeweb

OK, but there has to be a way to get the results I need. Use a nested query perhaps? How can I get the results to combine if the ids are the same and still pull information from a joined table?

ultraslacker

With GROUP BY table1.id, you will get one row per id. That's it.

What you want to do is order by table1.id, and use script to handle the presentation.