groub by query problems

[megetron]

Hi all,
I have a query, that I want to group by all the items in one field. but the group by query force me to group by, using all fields.
the query is this:
SELECT team.leauge,team.forum_name,team.forum_id,group_team.id,team.name,3*count( match.home_team_id) as points,sum(match.home_team_goals) as Goals_plus,sum(match.guest_team_goals) as Goals_minus from group_gvion,group_team,match_gvion, match,team where group_gvion.gvion_id=10 and group_gvion.group_id=group_team.id and (match.home_team_goals>match.guest_team_goals) and group_team.team_id=team.forum_id and (match.home_team_id=team.forum_id) and match_gvion.gvion_id=10 and match_gvion.match_id=match.id and match.level<=3 and match.status=1
group by team.leauge,team.forum_name, team.forum_id,group_team.id,team.name

UNION SELECT team.leauge,team.forum_name, team.forum_id,group_team.id,team.name,3*count(match.guest_team_id) ,sum(match.guest_team_goals),sum(match.home_team_goals) from group_gvion,group_team,match_gvion,match,team where group_gvion.gvion_id=10 and group_gvion.group_id=group_team.id and (match.home_team_goals<match.guest_team_goals) and group_team.team_id=team.forum_id and (match.guest_team_id=team.forum_id) and match.level<=3 and match_gvion.gvion_id=10 and match_gvion.match_id=match.id and match.status=1
group by team.leauge,team.forum_name, team.forum_id,group_team.id,team.name

is there any way group by only one field?

[Krzemo]

is there any way group by only one field? Yes - use one field in select:D

but the group by query force me to group by, using all fields.
lets consider table:

field1 field2 field3

1 2 3
1 1 1
1 2 1


and select:

SELECT field1,field2,COUNT(*) FROM table group by field1,field2

field1 field2 count
1 2 2
1 1 1

if U change it to this:


SELECT field1,COUNT(*) FROM table group by field1
field1 count
1 3


And what output U expect in this situation:


SELECT field1,field2 /*not groupped by*/,COUNT(*) FROM table group by field1

field1 field2 count
1 2 3 :confused:
1 1 3 :confused:

If it is your need than:

SELECT field1,field2,HowMany
FROM table,
(SELECT field1 FieldGroupped,COUNT(*) HowMany FROM table group by field1) tabgroup
WHERE table.field1=tabgroup.FieldGroupped


Best regards,
Krzemo