Re: Why using "Group By"

From: GoranG <no_at_spam.net>
Date: Wed, 19 Mar 2003 12:14:43 +0100
Message-ID: <q0jg7vsqqueph5e0ik52um4tajjgu7kmgk@4ax.com>

On 19 Mar 2003 02:24:01 -0800, pkl_at_mailme.dk (Peter Koch Larsen) wrote:

<cut>
>This is not legal SQL but wouldn't it be nice to have something like:
>
>select person.name, person.income/(sum (person.income) group by
>person.division) from person?
>
>The intention is for each person to detect how large a relative salary
>he earns within each division.
>

This should be legal SQL:

SELECT

	person.name, 
	person.income/(
		SELECT SUM(person.income) 
		FROM person 
		WHERE person.division = base.division)) AS

income_percent
FROM person AS base

with a note that this gives percentage... (not relative salary)

it could be that you need

SELECT

	person.name, 
	person.income/(
		SELECT AVG(person.income) 
		FROM person 
		WHERE person.division = base.division)) AS k

FROM person AS base

also note that if you encounter preformance problems with these queries these could be rewritten...

( GoranG79 AT hotmail.com ) Received on Wed Mar 19 2003 - 05:14:43 CST