"matti timonen" wrote:
>
>
> 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
Hi Matti
No need to do this on the client side.
Just do an additional GROUP BY:
SELECT
lc.location
, fc.name
, SUM (fd.sweight * lc.lweight) AS score
FROM
funcdef AS fd
LEFT JOIN loccri AS lc ON fd.criteria=lc.criteria
LEFT JOIN locations AS loc ON loc.ID=lc.location
WHERE
function=1
AND lc.criteria!=null
GROUP BY
lc.location
Tschau
Christian
