JOINs and SUMs Not Giving Expected Results

I have just added a third table to a query and I am no longer getting
the results I am expecting.

Three Tables:
CUSTINVOICEJOUR (Header Table)
CUSTINVOICETRANS (Line Item Table)
MARKUPTRANS (Additional Header Info)

CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.

CUSTINVOICEJOUR has a one to many relationship to MARKUPTRANS.

I need to sum an integer column from MARKUPTRANS, in rows that are
related to CUSTINVOICEJOUR, and include that output with my query
below, which right now has a row for each CUSTINVOICETRANS record:

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm')

The above works fine - a row for each record in CUSTINVOICETRANS with
the header info in there as well.

I tried the query below to add a SUM() from MARKUPTRANS, but when I run
it, I get one row with strange results in it - not what I expected.
What am I doing wrong?

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
SUM(MARKUPTRANS.VALUE) AS FreightValue
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID INNER JOIN
MARKUPTRANS ON CUSTINVOICEJOUR.RECID =
MARKUPTRANS.TRANSRECID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') AND (MARKUPTRANS.DATAAREAID =
'acm')
GROUP BY CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
MARKUPTRANS.MARKUPCODE
HAVING (MARKUPTRANS.MARKUPCODE = 'Freight')"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message news:ci581o$h9v@.odak26.prod.google.com...
> I have just added a third table to a query and I am no longer getting
> the results I am expecting.
<snip
If you're aggregating a column from MARKUPTRANS, then you shouldn't include another column from that same table in the
GROUP BY clause. I would rewrite that as a correlated subquery in the SELECT list.

SELECT
CUSTINVOICEJOUR.INVOICEAMOUNT,
CUSTINVOICETRANS.QTY,
(Select SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE CUSTINVOICEJOUR.RECID = MARKUPTRANS.TRANSRECID
and MARKUPTRANS.DATAAREAID = 'acm'
and MARKUPTRANS.MARKUPCODE = 'Freight' ) AS FreightValue

FROM
CUSTINVOICEJOUR
INNER JOIN
CUSTINVOICETRANS
ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID
WHERE
(CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') ;

--
Paul Horan[TeamSybase]