Group by not producing the results I wanted

NoviceMe
10-14-2004, 09:13 AM
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

Granty
10-14-2004, 09:24 AM
You need to GROUP BY team_id, MonthName(date_of_event);

NoviceMe
10-14-2004, 09:29 AM
Granty

it still does the same thing

Granty
10-14-2004, 09:31 AM
Can you post the whole SQL statement as it is now?

NoviceMe
10-14-2004, 09:34 AM
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);

Granty
10-14-2004, 09:50 AM
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 :(

NoviceMe
10-14-2004, 09:52 AM
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.

NoviceMe
10-14-2004, 11:16 AM
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!

Granty
10-14-2004, 11:17 AM
Weird!

Still, glad you are sorted :)

NoviceMe
10-14-2004, 11:24 AM
Spoke too soon, I changed the year and its back to square one - I am now very baffled :(

NoviceMe
10-14-2004, 12:44 PM
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

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum