
July 28th, 1999, 06:44 AM
|
|
Guest
|
|
Posts: n/a
Time spent in forums:
Reputation Power:
|
|
|
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
|