OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

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

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
arpeggio
General User
General User


Joined: 17 Nov 2009
Posts: 10

PostPosted: Sun Nov 22, 2009 12:30 am    Post subject: using multiple WHERE statements in one SELECT [SOLVED!!] Reply with quote

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


Last edited by arpeggio on Wed Dec 02, 2009 5:28 pm; edited 1 time in total
Back to top
View user's profile Send private message
FreewayFred
Power User
Power User


Joined: 22 Feb 2007
Posts: 85
Location: Wisconsin USA

PostPosted: Sun Nov 22, 2009 5:51 am    Post subject: Reply with quote

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

Code:
SELECT
   "pts"."tn" as "TeamNumber",
   "pts"."wn" as "WeekNumber",
   SUM(CASEWHEN( "pts"."wn" = 2, "pts"."pt", Null)) as "First",
   SUM(CASEWHEN( "pts"."wn" = 3, "pts"."pt", Null)) as "Second"
From "pts" as "pts"
Where "pts"."wn" BETWEEN 2 AND 3
Group by "pts"."tn", "pts"."wn"
Order by "pts"."tn", "pts"."wn"


Perhaps you can modify it to suit your purpose.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Nov 22, 2009 9:29 am    Post subject: Reply with quote

arpeggio:

Just to be clear ( at least an attempt to be as clear as mud Mr. Green ) . . . 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:

Code:
SELECT
   "Team",
   SUM(CASEWHEN( "WinByMargin" <= 5,1,0)) AS "Win By <= 5",
   SUM(CASEWHEN( "WinByMargin" >= 6, CASEWHEN( "WinByMargin" <= 10,1,0),0 )) AS "Win By 6 To 10",
   SUM(CASEWHEN( "WinByMargin" >= 11,1,0 )) AS "Win By 11 To 15"

FROM "ScoreTable" AS "ScoreTable"

GROUP BY "Team"

ORDER BY "Team"


I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
arpeggio
General User
General User


Joined: 17 Nov 2009
Posts: 10

PostPosted: Tue Dec 01, 2009 12:29 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
FreewayFred
Power User
Power User


Joined: 22 Feb 2007
Posts: 85
Location: Wisconsin USA

PostPosted: Tue Dec 01, 2009 3:48 am    Post subject: Reply with quote

Note that the last CASEWHEN is not followed by a comma.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Dec 01, 2009 8:54 am    Post subject: Reply with quote

arpeggio:

You said:

arpeggio wrote:
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!!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!!


The commands that are run from "the SQL window" ( I will assume you mean . . . from the Menu: Tools -> SQL... ) are commands that CHANGE the database . . . NOT commands that READ the database and return a Result Set. Such commands include
  1. ALTER
  2. CHECKPOINT
  3. CREATE
  4. DISCONNECT
  5. DROP
  6. INSERT
  7. SCRIPT
  8. SET
  9. UPDATE

On the other hand, when you want the Result Set . . . returning data from a database . . . you do this from a Query, Create Query in SQL View.....

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
arpeggio
General User
General User


Joined: 17 Nov 2009
Posts: 10

PostPosted: Tue Dec 01, 2009 4:38 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Dec 01, 2009 7:07 pm    Post subject: Reply with quote

arpeggio:

You said:
arpeggio wrote:
I hope u or someone can help.


I created a table by the name of "ScoreTable" exactly as you said it exists in your first post. Additionally, I entered into this table, the 'sample' data you provided in your first post.

I entered the SQL exactly as I wrote it in my first post . . . ran it . . . and the results are below, exactly as you you said you wanted it in your first post.



So, if you have an error, and, since I do not know what is, I have no idea what you are doing wrong. You have elected NOT to show the SQL you executed, therefore I cannot help you. Perhaps someone else can, but, not me.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
arpeggio
General User
General User


Joined: 17 Nov 2009
Posts: 10

PostPosted: Tue Dec 01, 2009 11:27 pm    Post subject: Reply with quote

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!!) Embarassed Crying or Very sad

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.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Dec 02, 2009 8:12 am    Post subject: Reply with quote

arpeggio:

arpeggio wrote:
what is the logic of using SUM?


For each time a TRUE value is returned, it makes it a 1.

For each time a FALSE value is returned, it makes it a 0.

Using SUM will ADD the number of TRUE values ( 1 ) and FALSE values ( 0 ).

Ergo, the result returned is what you want.

Simple logic and common sense.

I am finished posting here. It has been resolved.There is no more reason for me to waste anymore time on something very simple . . . where following the instructions was all that was needed. If you want to COUNT it, please go ahead. No reason for me to waste anymore of my time here, except, one last time to insist you follow the instructions on the LAST line in this post ( as in the LAST line of all my other posts in this thread ).

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group