I've tried a few of the suggestions so far, and I couldn't get any of them
to allow me to select more than just the sum and the primary_id and then I
tried the one from Bryan below and it worked like a charm, but I needed to
add another sum from another table and things got all screwy. Then I
(actually the other Tyler here) found a rather simple solution for the
query, and it allowed me to select more than just the sum and also I added a
second sum and everything became hunky-dory. Here's what I ended up with:
SELECT primary.primary_id, primary.initiated, primary.completed,
primary.sorted,
(SELECT SUM(secondary.received)
FROM secondary
WHERE secondary.primary_id = primary.primary_id) AS total,
(SELECT SUM(tertiary.error)
FROM tertiary
WHERE tertiary.primary_id = primary.primary_id) AS errors
FROM primary
thanks everyone for all the input, am I the king of run-on sentences, or
what...
Tyler Silcox
email | [EMAIL PROTECTED]
----- Original Message -----
From: "Bryan Love" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, February 08, 2002 12:17 PM
Subject: RE: I need to do a sum on a secondary (left outer join) table...
SELECT primary.primary_id, primary.initiated, primary.completed,
SUM(secondary.received)
FROM primary LEFT OUTER JOIN secondary
ON primary.primary_id = secondary.primary_id
GROUP BY primary.primary_id, primary.initiated, primary.completed
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
______________________________________________________________________
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
