Re: question about queries
Date: 1996/11/19
Message-ID: <56sr7t$ccm_at_zeus.mobile.com>#1/1
Wouldn't this achieve what you want (assuming that you are joining on the id column in each table):
SELECT id,
COUNT(vala)*vala,
COUNT(valb)*valb,
COUNT(valc)*valc
FROM a,b,c
WHERE a.id = b.id
AND a.id = c.id
GROUP BY id;
Hope this helps
Matt
mmarrow_at_pacbell.mobile.com
Jason Grace (jgrace_at_saltmine.radix.net) wrote:
: 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
Received on Tue Nov 19 1996 - 00:00:00 CET