469,282 Members | 1,637 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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')

Jul 20 '05 #1
1 2398
-P-
"Hunter Hillegas" <hu*************@gmail.com> wrote in message news:ci********@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]

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Prem | last post: by
1 post views Thread by Omavlana | last post: by
2 posts views Thread by raulgz | last post: by
4 posts views Thread by Scott Marquardt | last post: by
20 posts views Thread by p175 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.