no, you can group by all those fields.
this will give you a list of all ponumbers, their dates, the carrier, and
the quantity shipped for each combination of these three.
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-----Original Message-----
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 19, 2000 3:15 PM
To: CF-Talk
Subject: RE: JOIN and SUM
I need to select multiple fields from the master, here would be a better
example:
SELECT m.pon,m.podate,m.carrier,SUM(d.qty) AS TotalPoQty
FROM master m,
detail d
WHERE
m.podate BETWEEN 12/15/2000 AND 12/30/2000 AND
m.pon = d.pon
GROUP BY m.pon
This I know doesnt work and I get the same type of ODBC error. Is this
something that needs to be done in a Sub-Query?
Thanks,Adrian
-----Original Message-----
crap. of course i forgot that part.
you should/can group by them. it will select all the ponumbers in master.
what do you mean by "select multiple items from master as well"?
chris olive, cio
-----Original Message-----
The problem with this is ODBC says m.ponumber is invalid in the select list
because it is not part of the aggregate function....I guess I could use
GROUP BY but I really need to select multiple items from the master as
well....
-Adrian
-----Original Message-----
are you sure you want sum? or just a count of the entries in detail that
are for PO number?
try
SELECT
m.ponumber, sum(<whatever field you are summing) as thecount
FROM
master m,
detail d
WHERE
m.ponumber = d.ponumber
if you DON'T want sum, replace it with count(d.ponumber).
chris olive, cio
-----Original Message-----
Im joining a master and detail table, the relationship is a PO Number. The
Detail table contains quantities that I need the SUM of for each instance of
the matching PO in the Master table.
I know how to do the JOIN but how do I get the SUM portion, can this be done
within the JOIN query?
Example:
-----------------------------
Master Table
PO
A123456
B123456
-----------------------------
-----------------------------
Detail Table
PO QTY
A123456 2
A123456 5
A123456 6
B123456 8
-----------------------------
Output needs to look like this:
A123456 13
B123456 8
Thanks,Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
