I've got a query to retrieve invoice data. Some invoices have multiple lines and doubling up the totals. There's only 1 field in each that I need totaled.
Use Welland_export
SELECT
AR.ORDER_NO,
ar.invoice_nbr,
AR.CUST_NBR,
COALESCE(AR_MERCH_TOTALS.MER_AMT,0) AS MER_AMT,
COALESCE(AR_DISC_TOTALS.DISC_AMT,0) AS DISC_AMT,
(COALESCE(AR_MERCH_TOTALS.MER_AMT,0) - COALESCE(AR_DISC_TOTALS.DISC_AMT,0)) AS NETSALES,
COALESCE(AR_ADD_TOTALS.ADD_AMT,0) AS ADD_AMT,
AR_ADD_TOTALS.ADDIT_CD_LINE,
COALESCE(AR_FRT_TOTALS.FRT_AMT,0) AS FRT_AMT,
AR.INV_DATE,
AR.U2_ID
FROM ARIV AR
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARDISC1.DISC_AMT) AS DISC_AMT
FROM ARIV AR1
LEFT JOIN ARIV_DISC_DETAIL11 ARDISC1 ON AR1.U2_ID = ARDISC1.u2_id
WHERE AR1.INV_DATE BETWEEN @STARTDATE AND @ENDDATE
and ar1.u2_id not like '%*%M%'
GROUP BY AR1.U2_ID,AR1.ORDER_NO) AR_DISC_TOTALS ON AR.u2_id = AR_DISC_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARMERCH.MER_AMT) AS MER_AMT
FROM ARIV AR1
LEFT JOIN ARIV_MER_DETAIL11 ARMERCH ON AR1.U2_ID = ARMERCH.u2_id
WHERE AR1.INV_DATE BETWEEN @STARTDATE AND @ENDDATE
and ar1.u2_id not like '%%M%'
GROUP BY AR1.U2_ID,AR1.ORDER_NO) AR_MERCH_TOTALS ON AR.U2_ID = AR_MERCH_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID, AR1.ORDER_NO, SUM(ARADD.ADDIT_AMT) AS ADD_AMT, ARADD.ADDIT_CD_LINE
FROM ARIV AR1
LEFT JOIN ARIV_ADDL_DETAIL11 ARADD ON AR1.U2_ID = ARADD.u2_id
WHERE AR1.INV_DATE BETWEEN @STARTDATE AND @ENDDATE
and ar1.u2_id not like '%%M%'
GROUP BY AR1.U2_ID, AR1.ORDER_NO,ARADD.ADDIT_CD_LINE) AR_ADD_TOTALS ON AR.U2_ID = AR_ADD_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARFRT.FRT_AMT) AS FRT_AMT
FROM ARIV AR1
LEFT JOIN ARIV_FRT_DETAIL11 ARFRT ON AR1.U2_ID = ARFRT.u2_id
WHERE AR1.INV_DATE BETWEEN @STARTDATE AND @ENDDATE
and ar1.u2_id not like '%%M%'
GROUP BY AR1.U2_ID, AR1.ORDER_NO) AR_FRT_TOTALS ON AR.u2_id = AR_FRT_TOTALS.u2_id
WHERE AR.INV_DATE BETWEEN @STARTDATE AND @ENDDATE
and ar.u2_id not like '%%*M%'
Example -
I only need the ADD_AMT field totaled. The other fields totaled throw the report totals off.
EDIT - I've got the fields all correct by using Max() on all but the ADD_AMT field. Used Sum() on that. Now I've got to get the totals.