Group by not producing the results I wanted

Hi,

I am working with SQL Anywhere, and am trying to produce a summary of results with the following:

[vb
SELECT team_id, monthname(date_of_event),
SUM (total_staff), SUM (total_hours),
SUM (outcomes1), SUM (outcomes2),
SUM (outcomes3)
FROM event_results
WHERE year(date_of_event) = 2003
GROUP BY team_id, date_of_event;
/vb]
It returns something that looks like this

AT1-01 August 2 8
AT1-01 August 2 8
AT1-01 August 4 32
AT1-01 September 2 8
AT1-01 September 2 8
AT1-01 October 4 32
AT1-01 October 3 24
AT1-02 October 3 27

I want it to produce proper totals by team and by month like so:
AT1-01 August 8 48
AT1-01 September 12 64
AT1-01 October 4 32
AT1-02 October 10 83

what is missing from my query? I am afraid I am a total beginner with SQL :)

Thanks in advance

You need to GROUP BY team_id, MonthName(date_of_event);

Granty

it still does the same thing

Can you post the whole SQL statement as it is now?

Granty, here it is - i am baffled

SELECT team_id, MonthName(date_of_event),
SUM (total_staff), SUM (total_hours),
SUM (outcomes1), SUM (outcomes2),
SUM (outcomes3)
FROM event_results
WHERE year(date_of_event) = 2003
GROUP BY team_id, MonthName(date_of_event);

Im baffled too....

What is SQL anywhere? Ive never heard of it.

I recreated this scenario in Access and the code I gave you worked fine for me :(

Sybase SQL Anywhere with InfoMaker version 5....its a nightmare to use, but I have no choice - its what I have been made to use.

Granty]

it was silly in the end, it needed DISTINCT as follows:

SELECT DISTINCT team_id, MonthName(date_of_event),
SUM (total_staff), SUM (total_hours),
SUM (outcomes1), SUM (outcomes2),
SUM (outcomes3)
FROM event_results
WHERE year(date_of_event) = 2003
GROUP BY team_id, MonthName(date_of_event);

Thanks for your help, I am now a happy bunny!

Weird!

Still, glad you are sorted :)

Spoke too soon, I changed the year and its back to square one - I am now very baffled :(

Granty

finally got it to work now, so posting it here in case anyone else gets this problem:

SELECT DISTINCT team_id, DateFormat(date_of_event,'mmmmmmmmm'),
SUM (total_staff), SUM (total_hours),
SUM (outcomes1), SUM (outcomes2),
SUM (outcomes3)
FROM event_results
WHERE year(date_of_event) = 2003
GROUP BY team_id, DateFormat(date_of_event,'mmmmmmmmm');

:D