1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

sum(value) -> not properly ended???

Discussion in 'SQL PL/SQL' started by monkey, Jun 6, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Tried this:
    Code (SQL):
    SELECT SUM(VALUE) FROM price WHERE 3<pid<7;
    and this:

    Code (SQL):
    SELECT SUM(VALUE) FROM price HAVING 3<pid<7;
    Table is:
    Code (SQL):

                                       PID   VALUE
    -------------------------------------- -------
                                         7   20.33
                                         7 7659.86
                                         7  123.67
                                         1   20.33
                                         2 7659.86
                                         3  123.67
                                         2 7659.86
                                         3  123.67
                                         1 12345.33
                                         1 12345.33

     10 ROWS selected
    Please, any ideas, what is wrong?
    many thanks!!!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    This is not a valid Oracle SQL statement - most probably MYSQL syntax.

    The "3<pid<7" is most probably interpreted as select all rows where (3 < pid) or (pid < 7).
     
    monkey likes this.
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Please, can someone help me, how to write it in oracle?
    Just want sum from all rows where id is greater then 3 and samller then 7?
    btw, there are some ids that are identical. As I did not declare any primary key, is this the reason why it is ignored?
    many thanks!!!
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Try the following:

    Code (SQL):
    SELECT SUM(VALUE) FROM price WHERE (id > 3) AND (id < 7);
    or

    Code (SQL):

    -- Assuming id is Integer
    SELECT SUM(VALUE) FROM price WHERE id BETWEEN 4 AND 6;
     
    monkey likes this.
  5. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    MANY THANKS!!!
    Just one q: between includes the first number, but the last not, or? (oracle???)
    I mean, this would include: 4,5 but not 6 (or it was just for letters???)

    (think I got like this on w3school)

    many thanks!!!


    p.s. I do not have at the moment the proper acces neither to internet neither to any oracle base, so I APPOLOGISE for qs more stupid then suspected!!!
    than U all for understanding!!!
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    "id BETWEEN 4 and 6" (Oracle) = "4 <= id <= 6" (Maths.)
     
    monkey likes this.
  7. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Many thanks, Rajen!
    Does the same go for letter in oracle too?
    (sorry for questions, at moment with ot access to oracle database, so can't check lon!)

    Many thanks!!!
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes, same rule for characters (based on ASCII code):

    id BETWEEN 'D' AND 'G' = 'D'<= id <= 'G' , i.e. (D, E, F,G)
     
    monkey likes this.