#1
  1. No Profile Picture
    Herring
    Guest
    Devshed Newbie (0 - 499 posts)

    Hi,

    I need to multiply two columns in a query and then add the results together
    based on an id.

    I`ve already made a query:

    select lc.location,
    fc.name,
    fd.sweight*lc.lweight as score
    from funcdef fd
    left join
    loccri lc on fd.criteria=lc.criteria
    left join
    locations loc on loc.ID=lc.location
    where
    function=1 and lc.criteria!=null

    here`s the result:

    location name score
    5 market place 500
    4 post office 3750
    2 railwaystation 1000
    2 railwaystation 1000
    2 railwaystation 2000
    2 railwaystation 2000
    5 market place 1500
    2 railwaystation 1500
    2 railwaystation 1500
    5 market place 3000

    now I would need to get the cumulative results of each location. as in

    loc score
    5 5000
    2 9000
    4 3750

    i`ve done this in php, but i`d like to use sql queries since the table
    can grow to thousends of rows, and optimizing queries seems like a good
    idea.
    I don`t know how to do the second query.
    is there a way to do all of this in one query, or do I need to use temporary tables?

    - matti
  2. #2
  3. No Profile Picture
    DesertFox
    Guest
    Devshed Newbie (0 - 499 posts)
    Matti,

    This might spark some ideas.

    In an old dos database I use, I can issue this select command:

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    SELECT COL1, SUM(COL2 * COL3) FROM TABLE1 GROUP BY COL1 WHERE ....(Whatever)
    [/quote]

    I haven't looked at the select syntax in the MySQL document when I posted this message. Maybe it might require some tweaking.

    Hope this helps.

    DesertFox
  4. #3
  5. No Profile Picture
    Herring
    Guest
    Devshed Newbie (0 - 499 posts)
    Thanks, I got it working with this query...
    someone said that doing left joins is not very efficient... I'll do some optimizing on the query later, just had to get it working first.

    select
    lc.location
    , loc.name
    , sum(fd.sweight*lc.lweight) as score
    from
    funcdef fd
    left join loccri lc on fd.criteria=lc.criteria

    left join locations loc on loc.ID=lc.location

    where
    fd.function=(1 or 2)
    and lc.criteria!='null'
    group by
    lc.location
    order by
    score desc

    - Herring
  6. #4
  7. No Profile Picture
    Herring
    Guest
    Devshed Newbie (0 - 499 posts)
    fd.function=(1 or 2) is a typ0 and notin the actual query : fd.function=1, sorry
    - Herring

IMN logo majestic logo threadwatch logo seochat tools logo