Here's an example i use: The table names don't make sense, but the logic is
there
SELECT master.master_ID,
SUM(sub_table.cost) AS total
FROM master
INNER JOIN sub_table
ON master.master_ID = sub_table.master_ID
GROUP BY master.master_ID
jim.curran
technical.director
nylon.technology
six.west.fourteenth.street
new.york.ny.10011
212.691.1134
[EMAIL PROTECTED]
-----Original Message-----
From: Tyler Silcox [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 08, 2002 11:00 AM
To: CF-Talk
Subject: I need to do a sum on a secondary (left outer join) table...
Is there a way to grab all the records from one table and sum up all the
values of a column from another table based on a foreign key?
This is what I'm trying to do:
SELECT primary.primary_id, primary.initiated, primary.completed,
SUM(secondary.received)
FROM primary LEFT OUTER JOIN secondary
ON primary.primary_id = secondary.primary_id
I want to grab all of the rows from primary, but then I want to count
the received amount for every record in the secondary table that have
the id from primary...
so if I have 3 records in the primary table, and 15 records in the
secondary (5 per each record in primary), I just want to return the 3
rows from the primary and the sum of all the received columns in the
secondary pertaining to the primary's id...whew...
desired recordset:
1 initiated completed 50 (so this could be the sum of 5,
5, 5, 5, 5 [from 5 different rows in the secondary table with the
primary_id or 1])
2 initiated completed 222
3 initiated completed 0
Tyler Silcox
email | [EMAIL PROTECTED]
website | www.gslsolutions.com
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
