List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 28 1999 6:20pm
Subject:Re: math problem with multiple que
View as plain text  
"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

Thread
math problem with multiple queGeocrawler.com28 Jul
  • Re: math problem with multiple queChristian Mack28 Jul
    • Re: math problem with multiple queMartin Ramsch29 Jul