Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77

    Unanswered: SUM() function issue in SQL

    Here is a problem I've bounced on and couldn't find any possible nice solution to it. Maybe someone with his fresh ideas can help me ...

    Code:
    SELECT tbl1.user_id, SUM(tbl1.points) AS overall
      FROM tbl1 LEFT JOIN tbl2 ON tbl1.some_id = tbl2.some_id
      WHERE tbl2.some_condition = 'some_value'
      GROUP BY tbl1.user_id
      ORDER BY overall DESC;
    I need to SUM only those 'points' elements that are not equal to, say '99' ... Not all, but with some condition. How can it be done? Any advice?

    Why this problem appeared: I've made 'points' field value of '99' by default though usually it holds less then '10' in numbers. And I need to get SUM of those 'less than 10' numbers without involving huge '99' elements!

    I came to a possible solution of changing default type for the 'points' to 'no default', but that also gets up on the surface some huge amount of re-work on a lot of other site's scripts ... ... Want to avoid this work some way.

    P.S.: tbl2 is used only for conditioning of the query
    Yours faithfully,
    Yaroslav Zaremba

  2. #2
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    I've come to another way of calculating: before displaying the exact result I received using SQL query you can use modulus operator to find the remaining from division by 99 ...
    Code:
    $sum % 99 = $sum_i_am_looking_for
    But still would be the best to get this number in SQL query instead of getting it in a program ...
    Yours faithfully,
    Yaroslav Zaremba

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    should have used NULL

    NULL means no value, and is therefore not included in aggregates like SUM()

    automatically

    thus, you say SUM(tbl1.points) and you don't have to filter anything

    maybe next time, eh


    rudy

  4. #4
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    I will surely re-work my DB in nearest time to avoid problems in future ... Used definition of '99' as an empty field because when tried to use 'empty()' function in PHP it also gave me TRUE to the fields that actually hold '0' value ... ... Maybe I'm missing something?

    Ok, so then another question: how can I actually INSERT 'NULL' into the field? Is it possible using '.... SET field = NULL .... '?
    Yours faithfully,
    Yaroslav Zaremba

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, to insert a null, you can just not supply a value, and it should get null as the default, assuming you defined it NULL, not NOT NULL

    create table foo
    ( id integer auto_increment
    , bar1 integer null
    , bar2 char(5) null
    , bar3 datetime null
    )

    insert into foo
    (bar1, bar3)
    values
    (937,'2000-02-29')

    insert into foo
    (bar2, bar3)
    values
    ('w00t!','2003-01-31')

    insert into foo
    (bar1, bar2)
    values
    (23,'skidoo')

    select * from foo


    you were right about the UPDATE syntax

    update foo
    set bar2 = null
    where bar2 = 'skidoo'


    rudy

  6. #6
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Thank you very much! Very appreciate your help, already changed table's structure for the correct one and everything works just fine.
    Yours faithfully,
    Yaroslav Zaremba

Posting Permissions

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