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

[SOLVED]Table alias causes duplication

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


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

PostPosted: Fri Mar 27, 2009 11:41 am    Post subject: [SOLVED]Table alias causes duplication Reply with quote

I have a table called pts (for points) with TeamNumber, WeekNumber, Points although I shortened the column names.

Code:
SELECT "tn" as "TeamNumber", "f"."wn","f"."pt" as "first","s"."wn", "s"."pt"as "second" from "pts" as "f", "pts" as "s"
where "f"."wn" < 3
and
"s"."wn" > 2
and "f"."tn" = "s"."tn"


I'm trying to separate results where weeks < 3 from weeks > 2 so I can sum the "first" and "second" values but I'm getting duplicates. I've tried using "distinct" but to no avail.

This would be similar to generating sales totals by quarter. Sorry for the brief column headings but I get too many typos otherwise. I realize that I could use separate tables for the first and second periods' values but would like to keep all in one table if possible.

Perhaps someone can point me in the right direction.

Also, I could use enlightenment regarding the use of List and List= for posting.

Thanks


Last edited by FreewayFred on Tue Mar 31, 2009 3:53 am; edited 1 time in total
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: Fri Mar 27, 2009 2:05 pm    Post subject: Reply with quote

FreewayFred:

In your original post . . . your code looked like this, except, I just added line breaks, for readability.

Code:
SELECT
   "tn" as "TeamNumber",
   "f"."wn",
   "f"."pt" as "first",
   "s"."wn",
   "s"."pt"as "second"
From "pts" as "f", "pts" as "s"
Where "f"."wn" < 3
    and "s"."wn" > 2
    and "f"."tn" = "s"."tn"


If I understand what you want correctly, the following SQL code should work, using only one table:

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"


NOTES:
  1. The above SQL can be run, either 'directly' OR letting Base 'parse' it first. That means, you could have created the code, EITHER, with the Query Builder ( GUI - Graphical User Interface ) . . . OR . . . by entering it directly.

  2. By using the AGGREGATE ( Group By ) technique, able to 'control' which 'column' gets the result . . . combining an 'Aggregate Function' ( SUM ) with a 'CASEWHEN' ( CASEWHEN is like a spreadsheet IF function ).

  3. I elected to use a BETWEEN in the WHERE clause ( Where "pts"."wn" BETWEEN 2 AND 3 ), alternatively, could have written it as WHERE "pts"."wn" >= 2 AND "pts"."wn" <= 3

  4. Since this is an AGGREGATE Query, anything in the SELECT must either be in the Group By clause, OR, an Aggregate Function ( Sum, Count, Min, Max, Avg, Stddev_pop, Stddev_samp, Var_pop, Var_samp )

  5. You can of course, change the Order By clause, to SORT by TeamName, "pts", or, WHATEVER

You asked:
FreewayFred wrote:
Also, I could use enlightenment regarding the use of List and List= for posting.

The answer is, EACH NEW ITEM starts with [*]
So, for example, actual FORUM code of:
Code:
[list=1][*]This is the first line.
[*]This is the second line.
[*]This is the third line.[/list]

Produces the following LIST:
  1. This is the first line.
  2. This is the second line.
  3. This is the third line.
Code:

[list][*]This is the first line.
[*]This is the second line.
[*]This is the third line.[/list]

Produces the following LIST:
  • This is the first line.
  • This is the second line.
  • This is the third line.

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

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
FreewayFred
Power User
Power User


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

PostPosted: Tue Mar 31, 2009 3:52 am    Post subject: Reply with quote

Sorry for the delay. Here is what I ended up with:

Code:
SELECT
  "Teams"."TeamName",
   
   SUM(CASEWHEN( "pts"."wn" < 3, "pts"."pt", Null)) as "First",
   SUM(CASEWHEN( "pts"."wn" > 2, "pts"."pt", Null)) as "Second"

From "Teams", "pts"
where "Teams"."TeamNumber" = "pts"."tn"
Group by "Teams"."TeamName"
Order by "First" desc


Thanks for all the help.
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