using multiple WHERE statements in one SELECT [SOLVED!!]

[arpeggio]

Hi all.

I am using:

SELECT "Team", COUNT( "WeekNo" ) AS "WinbyLessThan5" FROM "ScoreTable" WHERE "WinByMargin" BETWEEN 0 AND 5 GROUP BY "Team"

for the following table of Football Scores:

ScoreTable:
Week No| Team | WinByMargin
1 | A | 4
2 | A | 8
3 | B | 3
4 | A | 9
5 | B | 12

to count the No. of times a team won by a particular margin:

i would like to GROUP which team won. So for example:

Team | WinbyLessThan5 | Win By> 6 < 10 | Win By >11 < 15 | ....etc
A |　1｜2 | 0 |
B | 1 | 0 | 1 |

The above QUERY works fine with just one WHERE statement to generate:

Team | [i] WinbyLessThan5[/i
A |　1 |
B | 1 |

BUT when i try add other WHERE statements to get the other margin columns like:

SELECT "Team",

COUNT( "WeekNo" ) AS "WinbyLessThan5" FROM "ScoreTable" WHERE "WinByMargin" BETWEEN 0 AND 5,

COUNT( "WeekNo" ) AS "Win By> 6 < 10" FROM "ScoreTable" WHERE "WinByMargin" BETWEEN 6 AND 10, GROUP BY "Team"

I get an ERROR !! I tried using other variations on the theme and even IF statements, although "IF" doesnt seem to work on OpenOffice. Can someone please suggest a solution to handling this type of query?

Thank you so much in advance.

best regards,
arpeggio

[FreewayFred]

Here is some code Sliderule provided to me. It enabled me to build a crosstab query.

[Sliderule]

arpeggio:

Just to be clear ( at least an attempt to be as clear as mud ) . . . per your description above, WHAT DO YOU WANT WHEN WinBy is 6 ? The "Alias" names do not handle that possibility. My point, I would suggest using <= and >= , not just > and < .

The following SQL should accomplish what you want, per your description above:

[arpeggio]

Thank you so much for the reply FreeWayFred and SlideRule. I've been away so couldnt continue until now.


I tried to use Sliderule's statement and something very strange is that i get an error when i run the query even from the SQL window!!

Error is: Unexpected token in statement [SELECT....

so when i removed the "," from after the SELECT "Team",

i got:

Unexpected token SUM, requires FROM in statement [SELECT....

after putting the FROM clause directly after the SELECT "Team",

i got:

Unexpected token: SUM in statement [SELECT....


Hmmm! What other permutation could i try? I wonder if it just doesnt understand the CASEWHEN clause?

or any other suggestions?

with many thanks and regards,

arpeggio

[FreewayFred]

Note that the last CASEWHEN is not followed by a comma.

[Sliderule]

arpeggio:

You said:

[arpeggio]

Good point SlideRule. Thks for this.

It was only in desperation i tried the TOOLS -> SQL!!

I first tried running the code in the Create Query in SQL View..... . The errors i mentioned are those from the Create Query in SQL View. I tried again this morning and still giving same errors.

I hope u or someone can help.

with many regards,

arpeggio

[Sliderule]

arpeggio:

You said:

[arpeggio]

Whoops!! yes you RULE Sliderule!! it worked!!

I'm too embarrased to post my stupid error!!

ok for benefit of others and expense of my embarresment:

i was using COUNT instead of SUM !! (sorry i didnt just copy and paste the code u wrote in an attempt to understand better!!)

my logic was: I am COUNTing rows in which the WinBy occurs.

what is the logic of using SUM? i am always thinking that SUM is for SUMming the values. How is this thinking obviously incorrect?

Is it that the CASEWHEN function returns a 1 or 0. so we are SUMming the no. of times this happens? but why can't we COUNT the no.of times this happens?

anyway great stuff. thank you.

[Sliderule]

arpeggio: