Re: question about queries

> In article <Pine.SOL.3.95.961118170129.24926A-100000_at_saltmine.radix.net>,
 Jason Grace <jgrace_at_saltmine.radix.net> writes:
> |> Lets say you have 3 tables, table A table B and table C.
> |>
> |> Say table A has info for different id's such as salary, name etc, on a
 

> |> one line per person basis. say for each line, there corresponds
> |> multiple lines in table B for that id, and similarly for table c.
> |>
> |> Now say tables A B and C have values valA, valB, and valC respectively
 

> |> as fields (for instance salary, payments), and I want to sum up these
> |> values for a given month.
> |>
> |> What is the fastest most efficient way to do this correctly? Say for
> |> id '01' John Doe, valA=500 and there are 3 rows in table B with
 valB=500,
> |> and 10 rows in table C with valC =500 for this same guy. My result
 should
> |> give :
> |> sum(valA)=500, sum(valB)=1500, sum(valC)=5000
> |> but the select statement:
> |> SELECT SUM(VALA), SUM(VALB), SUM(VALC) FROM A, B, C
> |> WHERE A.ID=B.ID AND B.ID=C.ID AND MONTH=..
> |> would give the result of:
> |> 30*500=5000, 30*500=5000, 30*500=5000 for valA, valB and valC
> |> respectively.
> |>
> |> Summing each one individually or in subloops would take much longer
 than
> |> one query, so what's a neat way to do it really quick? Thanks.
> |>
> |> Jason, jgrace_at_radix.net, http://www.wavecon.com
> |>
 

How about you create a view on table B which holds a sum of valB grouped by id and month as in:

CREATE VIEW B_aggregated (id, month, valBsum) AS

	SELECT 	id, month, sum(valB)
	FROM		B
	GROUP BY	id, month;

and create a similar view on C. Then your solution would be:

SELECT	valA, valBsum, valCsum
FROM		A, B_aggregated, C_aggregated
WHERE	A.id = B_aggregated.id
AND		A.id = C_aggregated.id
AND		A.month = B_aggregated.month
AND		A.month = C_aggregated.month

Paul Routledge,
Glasgow,
Scotland. Received on Thu Nov 28 1996 - 00:00:00 CET