Results 1 to 4 of 4

Thread: query help

  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unanswered: query help

    I have the following query. I want it to return:

    screen_name, email, sum1, sum2

    I get the correct results, but I get multiple rows for the same screen_name. How can I change this query so I only get 1 row for each screen_name?

    select
    plyr.screen_name, plyr.email,
    (
    select nvl(sum(points),0) as TOURN
    from
    gm_point_log log
    where
    type=1 and method=2 and
    log.plyr_id=plyr.id and
    to_char(log.time_stamp, 'YYYY-MM') = '2003-08'
    ),
    (
    select nvl(sum(points),0) as RAKE
    from
    gm_point_log log
    where
    type=1 and method=1 and log.plyr_id=plyr.id and
    to_char(log.time_stamp, 'YYYY-MM') = '2003-08'
    )
    from
    acnt_plyr plyr,
    gm_point_log log
    where
    log.plyr_id=plyr.id

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    You could try this:

    select
    plyr.screen_name, plyr.email
    ,sum(case log.type=1 and log.method=2
    then nvl(log.points,0) else 0 end) as TOURN
    ,sum(case log.type=1 and log.method=1
    then nvl(log.points,0) else 0 end) as RAKE
    from
    acnt_plyr plyr,
    gm_point_log log
    where log.plyr_id(+)=plyr.id
    and to_char(log.time_stamp(+), 'YYYY-MM') = '2003-08'
    Group By
    plyr.screen_name, plyr.email;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    21
    hi
    thanks for the help,
    but it says missing right parenthesis when i try to run it

    sorry im a total newb.

  4. #4
    Join Date
    Jul 2003
    Posts
    21

    Thumbs up

    oh nevermind i got it!
    it has to be case when ...

    Thanks so much LKBrwn_DBA !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •