multiple queries and math???

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

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

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

fd.function=(1 or 2) is a typ0 and notin the actual query : fd.function=1, sorry
- Herring